mysql基础

蜂中

SQL语句

SQL语句分类

• SQL分类：
  
  
  
  • 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
    
  • 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
    
  • 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。DBA
    
  • 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等
    
  
  

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾
  
• 可使用空格和缩进来增强语句的可读性
  
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
  
  
  
  • 例如：SELECT * FROM user。
    
  
  
• 同样可以使用/**/的方式完成注释
  
• MySQL中的我们常使用的数据类型如下
  

**类型名称 **说明int（integer）整数类型double小数类型decimal（m,d）指定整数位与小数位长度的小数类型date日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒datetime日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒timestamp日期类型，时间戳varchar（M）文本类型， M为0~65535之间的整数DDL之数据库操作：database

创建数据库

格式：

1. create database 数据库名;
2. create database 数据库名 character set 字符集;

复制代码

例如：

1. #创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
2. CREATE DATABASE webdb_1;
3. #创建数据库 并指定数据库中数据的编码
4. CREATE DATABASE webdb_2 CHARACTER SET utf8;

复制代码

查看数据库

查看数据库MySQL服务器中的所有的数据库:

1.   show databases;

复制代码

查看某个数据库的定义的信息:

1. show create database 数据库名;

复制代码

例如：

1. show create database webdb_1;

复制代码

删除数据库

1. drop database 数据库名称;

复制代码

例如：

1. drop database webdb_2;

复制代码

使用数据库

• 查看正在使用的数据库:
  

1. select database();

复制代码

• 其他的数据库操作命令
  切换数据库：
  

1. use 数据库名;

复制代码

1. 例如：

复制代码

1. use webdb_1;

复制代码

DDL之表操作：table

创建表

• 格式：
  

1. create table 表名(
2.    字段名 类型(长度) [约束],
3.    字段名 类型(长度) [约束],
4.    ...
5. );
7. 类型：
8.         varchar(n)        字符串
9.         int                        整形
10.         double                浮点
11.         date                时间
12.         timestamp        时间戳
13. 约束：(详情查看第四章)
14.         primary key        主键，被主键修饰字段中的数据，不能重复、不能为null。

复制代码

• 例如：创建分类表
  

1. CREATE TABLE category (
2.   cid INT primary key, #分类ID
3.   cname VARCHAR(100) #分类名称
4. );

复制代码

查看表

• 查看数据库中的所有表：
  

1. 格式：show tables;

复制代码

• 查看表结构：
  

1. 格式：desc 表名;
2. 例如：desc category;

复制代码

删除表

• 格式：drop table 表名;
  

1. 例如：drop table category;

复制代码

修改表结构格式

• alter table 表名 add 列名 类型(长度) [约束];
  作用：修改表添加列.
  

1. 例如：#1，为分类表添加一个新的字段为 分类描述 varchar(20)
2. ALTER TABLE category ADD `desc` VARCHAR(20);

复制代码

• alter table 表名 modify 列名 类型(长度) 约束;
  作用：修改表修改列的类型长度及约束.
  

1. 例如：#2, 为分类表的描述字段进行修改，类型varchar(50) 添加约束 not null
2. ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;

复制代码

• alter table 表名 change 旧列名 新列名 类型(长度) 约束;
  作用：修改表修改列名.
  

1. 例如：#3, 为分类表的分类名称字段进行更换 更换为 description varchar(30)
2. ALTER TABLE category CHANGE `desc` description VARCHAR(30);

复制代码

• alter table 表名 drop 列名;
  作用：修改表删除列.
  

1. 例如：#4, 删除分类表中description这列
2. ALTER TABLE category DROP description;

复制代码

• rename table 表名 to 新表名;
  作用：修改表名
  

1. 例如：#5, 为分类表category 改名成 category2
2. RENAME TABLE category TO category2;

复制代码

• alter table 表名 character set 字符集(了解);
  作用：修改表的字符集
  

1. 例如：#6, 为分类表 category 的编码表进行修改，修改成 gbk
2. ALTER TABLE category CHARACTER SET gbk;

复制代码

DML数据操作语言

插入表记录：insert

• 语法：
  

1. -- 向表中插入某些字段
2. insert into 表 (字段1,字段2,字段3..) values  (值1,值2,值3..);
3. -- 向表中插入所有字段,字段的顺序为创建表时的顺序
4. insert into 表 values (值1,值2,值3..);

复制代码

• 注意：
  
  
  
  • 值与字段必须对应，个数相同，类型相同
    
  • 值的数据大小必须在字段的长度范围内
    
  • 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
    
  • 如果要插入空值，可以不写字段，或者插入null。
    
  
  
• 例如：
  

1. INSERT INTO category(cid,cname) VALUES('c001','电器');
2. INSERT INTO category(cid,cname) VALUES('c002','服饰');
3. INSERT INTO category(cid,cname) VALUES('c003','化妆品');
4. INSERT INTO category(cid,cname) VALUES('c004','书籍');
6. INSERT INTO category(cid) VALUES('c005');
7. INSERT INTO category(cname,cid) VALUES('耗材','c006');

复制代码

更新表记录：update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

• 语法：
  
  
  
  • 更新所有记录的指定字段
    
  1. update 表名 set 字段名=值,字段名=值,...;

  复制代码
  
  
  • 更新符合条件记录的指定字段
    
  1. update 表名 set 字段名=值,字段名=值,... where 条件;

  复制代码
• 注意：
  
  
  
  • 列名的类型与修改的值要一致.
    
  • 修改值得时候不能超过最大长度.
    
  • 除了数值类型外，其它的字段类型的值必须使用引号引起
    
  
  

删除记录：delete

• 语法：
  

1. delete from 表名 [where 条件];

复制代码

SQL约束

主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

• 主键必须包含唯一的值。
  
• 主键列不能包含 NULL 值。
  
• 每个表都应该有一个主键，并且每个表只能有一个主键。
  

添加主键约束

• 方式一：创建表时，在字段描述处，声明指定字段为主键：
  

1. CREATE TABLE persons
2. (
3.   id_p int PRIMARY KEY,
4.   lastname varchar(255),
5.   firstname varchar(255),
6.   address varchar(255),
7.   city varchar(255)
8. )

复制代码

• 方式二：创建表时，在constraint约束区域，声明指定字段为主键：
  
  
  
  • 格式：[constraint 名称] primary key (字段列表)
    
  • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
    
  • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。
    
  
  

1. CREATE TABLE persons
2. (
3. firstname varchar(255),
4. lastname varchar(255),
5. address varchar(255),
6. city varchar(255),
7. CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
8. )

复制代码

• 方式三：创建表之后，通过修改表结构，声明指定字段为主键：
  
  
  
  • 格式：ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
    
  
  

1. CREATE TABLE persons
2. (
3. firstname varchar(255),
4. lastname varchar(255),
5. address varchar(255),
6. city varchar(255)
7. )
8. ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

复制代码

删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

1. ALTER TABLE persons DROP PRIMARY KEY

复制代码

自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

• 下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键
  

1. CREATE TABLE persons
2. (
3. p_id int PRIMARY KEY AUTO_INCREMENT,
4. lastname varchar(255),
5. firstname varchar(255),
6. address varchar(255),
7. city varchar(255)
8. )

复制代码

• 向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：
  

1. INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
2. INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')

复制代码

• 扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：
  

1. ALTER TABLE persons AUTO_INCREMENT=100

复制代码

• 面试题
  

问：针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区别？

1. 删除方式：
2.         delete 一条一条删除，不清空auto_increment记录数。
3.         truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

复制代码

非空约束

NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

• 下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：
  

1. CREATE TABLE persons
2. (
3. id_p int NOT NULL,
4. lastname varchar(255) NOT NULL,
5. firstname varchar(255),
6. address varchar(255),
7. city varchar(255)
8. )

复制代码

唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
添加唯一约束

与主键添加方式相同，共有3种

• 方式一：创建表时，在字段描述处，声明唯一：
  

1. CREATE TABLE persons
2. (
3. id_p int UNIQUE,
4. lastname varchar(255) NOT NULL,
5. firstname varchar(255),
6. address varchar(255),
7. city varchar(255)
8. )

复制代码

• 方式二：创建表时，在约束区域，声明唯一：
  

1. CREATE TABLE persons
2. (
3. id_p int,
4. lastname varchar(255) NOT NULL,
5. firstname varchar(255),
6. address varchar(255),
7. city varchar(255),
8. CONSTRAINT 名称UNIQUE (Id_P)
9. )

复制代码

• 方式三：创建表后，修改表结构，声明字段唯一：
  

1. ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

复制代码

删除唯一约束

• 如需撤销 UNIQUE 约束，请使用下面的 SQL：
  

1. ALTER TABLE persons DROP INDEX 名称

复制代码

• 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
  

默认约束

default 约束 用于指定字段默认值。当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。

• 添加默认约束，在创建表时候添加
  

1. CREATE TABLE t_user(user_id INT(10) DEFAULT 3);

复制代码

• 删除默认约束
  

1. ALTER TABLE t_user MODIFY user_id INT(10);

复制代码

SQL语句(DQL)

准备工作

1. #创建商品表：
2. create table product(
3.         pid int primary key auto_increment,
4.         pname varchar(20),
5.         price double,
6.         category_id varchar(32)
7. );
8. INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
9. INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
10. INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
11. INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
12. INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
13. INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
14. INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
15. INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
16. INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
17. INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
18. INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
19. INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
20. INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

复制代码

语法

1. select [distinct]
2. * | 列名,列名
3. from 表
4. where 条件

复制代码

简单查询

• 练习
  

1. #.查询常量值
2. SELECT 100;
3. SELECT 'hello';
4. #.查询表达式
5. SELECT 100%98;
6. #.查询函数
7. SELECT NOW();
8. SELECT VERSION();
9. #两个操作数都为数值型，则做加法运算
10. SELECT 100+90;
11. #只要其中一方为字符型，试图将字符型数值转换成数值型 如果转换成功，则继续做加  法运算
12. SELECT '123'+90;
13. #如果转换失败，则将字符型数值转换成0
14. SELECT 'hello'+90;       
15. #只要其中一方为NULL，则结果肯定为NULL
16. SELECT NULL+10;
17. #连接字符串
18. SELECT CONCAT('a','b','c')

复制代码

1. #查询所有的商品.       
2. select * from product;

复制代码

1. #查询商品名和商品价格.
2. select pname,price from product;

复制代码

1. #别名查询.使用的关键字是as（as可以省略的）.表别名:
2. select * from product as p;

复制代码

1. #别名查询.使用的关键字是as（as可以省略的）.列别名:
2. select pname as pn from product;

复制代码

1. #去掉重复值.
2. select distinct price from product;

复制代码

1. #查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
2. select pname,price+10 from product;

复制代码

条件查询

比较运算符> < >= 100);

• 练习
  

1. #查询商品名称为“花花公子”的商品所有信息：
2. SELECT * FROM product WHERE pname = '花花公子'

复制代码

1. #查询价格为800商品
2. SELECT * FROM product WHERE price = 800

复制代码

1. #查询价格不是800的所有商品
2. SELECT * FROM product WHERE price != 800
3. SELECT * FROM product WHERE price <> 800
4. SELECT * FROM product WHERE NOT(price = 800)

复制代码

1. #查询商品价格大于60元的所有商品信息
2. SELECT * FROM product WHERE price > 60;

复制代码

1. #查询商品价格在200到1000之间所有商品
2. SELECT * FROM product WHERE price >= 200 AND price <=1000;
3. SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

复制代码

1. #查询商品价格是200或800的所有商品
2. SELECT * FROM product WHERE price = 200 OR price = 800;
3. SELECT * FROM product WHERE price IN (200,800);

复制代码

1. #查询含有'霸'字的所有商品
2. SELECT * FROM product WHERE pname LIKE '%霸%';
4. #查询以'香'开头的所有商品
5. SELECT * FROM product WHERE pname LIKE '香%';
7. #查询第二个字为'想'的所有商品
8. SELECT * FROM product WHERE pname LIKE '_想%';

复制代码

1. #商品没有分类的商品
2. SELECT * FROM product WHERE category_id IS NULL
4. #查询有分类的商品
5. SELECT * FROM product WHERE category_id IS NOT NULL

复制代码

分组查询

分组查询是指使用group by字句对查询信息进行分组。

• 格式：
  

1. SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
2. #ASC 升序 (默认)
3. #DESC 降序

复制代码

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

• having与where的区别:
  
  
  
  • having是在分组后对数据进行过滤.
    where是在分组前对数据进行过滤
    
  • having后面可以使用分组函数(统计函数)
    where后面不可以使用分组函数。
    
  
  

练习：

1. #使用价格排序(降序)
2. SELECT * FROM product ORDER BY price DESC;

复制代码

1. #在价格排序(降序)的基础上，以分类排序(降序)
2. SELECT * FROM product ORDER BY price DESC,category_id DESC;

复制代码

分页查询

1. #显示商品的价格(去重复)，并排序(降序)
2. SELECT DISTINCT price FROM product ORDER BY price DESC;

复制代码

来源:https://www.cnblogs.com/paopaoT/p/17359900.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！