MySQL常用语法

命令行客户端MySQL的使用

登录命令

mysql -uroot -p  

退出命令

• exit
• quit

创建数据库

-- 创建数据库
create database test charset=utf8;

-- 查看/切换数据库
use test;

创建表

语法：

create table 表名(
  	字段名 类型  约束,
      ....
);

create table user(
  userid int(11) unsigned primary key unique,
  username varchar(16) not null,
  sex char(2) check(sex in ('男','女')),
  grade intger check(grade between 0 and 100),
  password varchar(18) not null,
  price double(10,2) 
);

insert into user values(1,'张三','男',86,123,3500.00),(2,'李四','男',92,123,4800.00),(3,'小红','女',90,123,3200.00);

-- 查询表中数据
select * from user;

-- 复制表结构到新表
create table aaa as select * from d_user limit 0;
create table aaa select * from d_user where 1=2;    -- (无法复制表结构中的主键类型和自增方式)
create table aaa like d_user;     -- (所有字符类型都复制到新表)

-- 复制表结构及数据到新表
create table bbb select * from d_user;

MySQL数据库的增删改查的操作

-- 查看数据库
show databases;     -- 查看数据库服务器中有多少数据库
show create database test;    -- 查看创建库的语法
select database();    -- 查看正在使用哪个库

-- 修改数据库(修改数据库的字符集和校对规则)
alter database test character set utf8 collate utf8_bin;

-- 删除数据库
drop database test; 

数据库表的增删改查操作

-- 查看表
     -- 1.查看库中有哪些表
     show tables; 	-- 查看库中有哪些表
     -- 2.查看创建表的语法 
     show create table user;
     -- 3.查看表结构
     desc user;
     
-- 删除表
drop table user;

-- 修改表
     -- 1.修改表添加列(alter table 表名 add 字段 类型（长度）约束)
     alter table user add sex varchar(2) not null;
     -- 2.修改表修改列的类型长度约束(alter table 表名 modify 字段名 类型(长度) 约束;)
     alter table user modify sex varchar(10) not null;
     -- 3.修改表，修改列名(alter table 表名 change 旧字段名 新字段名 类型(长度) 约束;)
     alter table user change sex gender varchar(20);
     -- 4.修改表的字符集(alter table 表名 character set 字符集)
     alter table user character set utf8;
     -- 5.删除表字段(alter table 表名 drop 字段名)
     alter table user drop gender;
     -- 6.重命名表(rename table 旧表名 to 新表名)
     rename table user to usertet;
     rename table usertet to user;

数据库表数据的增删改查操作

插入数据【insert】

 语法

-- 给某几列插入数据：insert into 表名(列1,列2,列3…) values(值1,值2,值3…);
insert into user(userid, username, password, price, gender) values (4, 'aaa', 123, 4250.00, '男');

-- 给所有列插入数据：insert into 表名values(值1,值2,值3…);
insert into user values (5, 'bbb', 123, 6250.00, '男');

-- 一次性插入多行数据: insert into 表名values(值1,值2,值3…),(值1,值2,值3…),(值1,值2,值3…)…;
insert into user values (6, 'ccc', 123, 3800.00, '女'),(7, 'qqq', 123, 5450.00, '男');

修改数据【update】

 注意：如果没有带条件，那么就是把这一列的值都修改了。

-- update 表名 set 列名1=值1, 列名2=值2 … [where条件];
update user set username='abc', gender='男' where userid=1;

删除数据【delete】

  物理删除：真正意义上从表中删除数据

-- 语法：
delete from 表名 [where 条件]
delete from students where username='张三';

/** delete from 表名 和 truncate table 表名 
两种方式的区别：  
      delete from 表名：是dml语句，一条一条的删除数据，事务可以作用在dml语句上。
      truncate table 表名：是ddl语句，先删除表，再创建一个跟之前表一样结构的新表，事务不能作用在ddl语句上。 */

  逻辑删除：根据标识字段修改数据，没有真正意义上从表中删除数据

-- 给表添加一个删除标识字段
alter table students add is_del bit default 0;
-- 逻辑删除，其实是修改表中的某个字段数据，没有真正意义上删除表中数据
update students set is_del = 1 where id = 3;
-- 查询没有删除的用户
select * from students where is_del = 0;
-- 查询已经删除的用户
select * from students where is_del = 1;

查询数据【select】

基本查询语句

-- 查询所有列(select * from 表名)
select * from user;

-- 查询指定列(select列1，列2…from 表名)
select userid, username from user;

--  给字段或者表设置别名 【as】    （as一般可以省略）
select name as 姓名, age 年龄, height 身高 from user;

-- 去除重复行 【distinct】
select distinct userid, username from user;

条件查询 【where】

-- 比较运算符：  >    >=   <    <=    != 或 <>：不等于  
-- 查询用户姓名不等于“张三”的用户  
select * from user where username!='张三';

-- 逻辑运算符：and：并且   or：或   not：取反 
-- 查询年龄不在18岁到30岁之间的用户: 
select * from user where not (age>=18 and age<=30);

-- 模糊查询：【like】
    --  %：任意多个任意字符
    --  _：一个任意字符
    select * from student where name like '_四';
    select * from student where name like '%四' or sex like '男';

-- 范围查询：
    -- between  and ：在一个连续的范围内查询
    -- in：在一个非连续的范围内查询    

    -- 查询id不在1-2之间的且是男生的数据   
    select * from student where not (id between 1 and 2) and sex='女';
    -- 查询表中姓名为“张三”或“李四”的数据	
    select * from student where name in ('张三','李四');

-- 空判断查询: 
    -- is null ：判断为空
    -- is not null ：判断不为空       
    select * from student where height is null;      
    select * from student where height is not null;

排序： 【order by】

   asc :升序
   desc :降序)

  不指定默认为升序 asc

-- select * from 表名 [where 条件] order by 列名 asc/desc,列名 asc/desc …
select * from user where username='李四' order by userid desc;
-- 查询表中最后三行数据
select * from (select * from student order by id desc limit 3) st order by st.id asc;

分页： limit 开始行索引,条数

  (开始行索引不指定默认从0开始，也就是从第一行获取数据)

  分页公式：(n-1) * m, m

-- 查询前3行男生信息并且身高大于1.7
select * from students where sex='男' and height > 1.7 limit 0, 3
-- 开始行索引可以不知道，默认是从第一行开始取值
select * from students where sex='男' and height > 1.7 limit 3

聚合函数：用于对表中的数据进行统计和计算

常用的聚合函数有：
  count：统计行数
  max：获取最大值
  min：获取最小值
  sum：求总和
  avg：求平均值

-- 提示：聚合函数默认忽略字段为null的记录，要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。
-- ifnull(字段名, 默认值)：当判断列对应的值为null的时候，则使用提供的默认值参与计算。
select count(*) 总条数, sum(age) 总年龄, max(age) 最大年龄, min(age) 最小年龄,avg(ifnull(age,18)) 平均年龄 from student;

分组查询：
  group by 分组字段 having 条件

分组使用的注意点:

• 对表中的某个字段进行分组，查询的时候只能查询分组字段的数据。
• 分组可以结合聚合函数使用，统计和计算的都是分组后的相关信息。

/** 注意：where子句中不能使用聚合函数，如想使用带有聚合函数的条件进行过滤数据，需使用having关键字
         having：用来过滤分组后的数据。having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
         group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割  */

-- 根据sex字段来分组
select sex from students group by sex;
-- 根据name和sex字段进行分组
select name, sex from students group by name, sex;select name, height from students group by name, height;
-- 根据sex字段进行分组，查询sex字段和分组的name字段信息
select sex, group_concat(name) from students group by sex;


-- sql语句的基本顺序	
select ... from 表名 where 条件 group by 分组字段 having 筛选后条件 order by 排序字段

外键

  作用：外键字段的只要来源于主表中的某个字段，验证数据的有效性，只有外键字段的值在主表中存在则才能添加或者修改

-- 外键约束  
foreign key create table 表名(字段名 类型(长度) 约束,	字段名 类型(长度) 约束,	foreign key (外键字段)  reference  关联表(关联字段))

-- 在表创建成功之后添加外键
-- alter table 表名  add  foreign key (外键字段) references 关联表(关联字段)

-- 添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
alter table teacher add foreign key(s_id) references school(id); 

-- 删除外键约束: alter table 表名 drop foreign key 外键约束名;    
show create table teacher;   

-- 查询外键的约束    
alter table teacher drop  foreign key teacher_ibfk_1;    

/** 注意:          
      1.添加外键时外键指向的表必须存在
      2.添加外键时外键指向的字段必须是主键     
      3.如果表中已经有数据,那么数据必须符合约束 */

连接查询

  当查询数据的时候来源于不同表，此时需要使用连接查询

内连接 【inner join】

  二张表的交集部分(不同表中的公共数据)

-- 显式内连接  
-- select * from 表a inner join 表b on 表a.id = 表b.id
select * from students st inner join classes cs on st.c_id = cs.cid;

-- 隐式内连接
-- select * from 表a ,表b where 表a.id = 表b.id 
select * from students st, classes cs where st.c_id = cs.cid;

外连接 【outer join】

  outer 可以省略

 左[外]连接 【left [outer] join】
   左表的所有数据与二张表的交集(左表的所有数据与满足连接条件的数据组合)

-- 语法
-- select * from 表a left [outer] join  表b on 表a.id = 表b.id
select st.*, cs.name from students st left join classes cs on st.class_id = cs.id;
-- *提示: 左连接查询是根据左表查询右边数据，右表有符合条件的数据则显示，否则显示null，左表的所有数据都会显示

 右[外]连接 【right [outer] join】
   右表的所有数据与二张表的交集(右表的所有数据与满足连接条件的数据组合)

--语法：
-- select * from 表a right [outer] join 表b on 表a.id = 表b.id
select st.*, cs.name from students st right join classes cs on st.class_id = cs.id;
-- *提示: 右连接查询是根据右表查询左边数据，左表有符合条件的数据则显示，否则显示null，右表的所有数据都会显示

交叉连接【cross join】

-- 语法：
-- select * from 表a cross join 表b	-- select * from 表a,表b
select * from students st cross join classes cs on st.c_id = cs.cid;	
select * from students st, classes cs where st.c_id = cs.cid;

自连接查询

  把同一张表看成两张表，然后做连接查询。

-- 语法：
-- select 表1.字段，表2.字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2
-- 查询湖北省的城市信息
select t1.*, t2.title as p_name from areas t1 inner join areas t2 on t1.pid = t2.id where t2.title = '湖北省';

子查询

  在一个查询语句里面又嵌套使用了一个查询语句，被嵌套的查询语句称为子查询。
  子查询是一个单独可以执行的sql语句。
  提示：查询的结果也可以做为一张表和其它表在做连接查询。

-- 语法：
select * from 表名 where 列名 = (select 列名 from 表名);
-- 查询大于平均年龄的学生:
select * from students where age > (select avg(age) from students);

-- 查询学生在班的所有班级名字:	
select name from classes where id in (select distinct class_id from students where class_id is not null);	

-- 查询年龄最大,身高最高的学生:	
select * from students where age = (select max(age) from students) and height = (select max(height) from students);	
select * from students where (age, height) = (select max(age), max(height) from students);	

-- 使用子查询湖北省下面的城市	
select t2.title as province,t1.* from (select * from areas where pid = (select id from areas where title='湖北省')) t1 inner join areas t2 on t1.pid=t2.id;

扩展

将查询结果插入到指定表中

-- 语法：	
insert into 表名(列名1,列名2) select 列名1,列名2 from 表名2	

-- 创建商品分类表    
create table goods_cates(id int unsigned primary key auto_increment not null, name varchar(30) not null);
-- 获取商品表里面的商品分类  
select distinct cate_name from goods;   
-- 把商品分类结果插入到goods_cates    
insert into goods_cates(name) select distinct cate_name from goods;

使用连接更新表中某个字段数据

-- 语法：
update 连表查询语句 set 表1.字段1 = 表2.字段2

-- 1. 先连表查询 
select * from goods gs inner join goods_cates gcs on gs.cate_name = gcs.name;    
-- 2. 把连表查询的结果作为一张虚拟表，进行数据的更新。    
update goods gs inner join goods_cates gcs on gs.cate_name = gcs.name set gs.cate_name = gcs.id;

创建表的时候给表中的某个字段添加数据

 语法：

create table 表名 (列1 类型 约束, 列2 类型 约束, ...) 
select 列名 as 列2 from 表名;

-- 创建商品品牌表
create table goods_brand(id int unsigned primary key auto_increment not null, name varchar(30) ) select distinct brand_name as name from goods;

 提示：给表中字段添加数据的时候，查询字段的名字一定要和表中的字段名一样

函数

  函数分为：系统函数、自定义函数

系统函数

字符串相关的函数

-- concat(str1,str2,...) 拼接字符串
select concat('A', 'B', 123) as str;
elect concat('“',name,'”') from student;

-- char_length(str) 统计字符的个数
select char_length('统计字符的个数');

-- left(str, len) 返回字符串str的左端len个字符
select left(name, 1) from students;

-- right(str, len) 返回字符串str的右端len个字符
select right(name, 1) from students;

-- substring(str,position,len) 返回字符串str的postion位置起的len个字符
select substring(name, 2) from students;

-- ltrim(str) 返回去除左边空格的字符串str
select ltrim('   abc   ');
select char_length(ltrim('   abc   '));

-- rtrim(str) 返回去除右边空格的字符串str
select rtrim('   abc   ');
select char_length(ltrim('   abc   '));

-- trim(str) 返回去除左右两边空格的字符串str
select trim('   abc   ');
select char_length(trim('   abc   '));

-- lower(str) 返回小写的str字符串
select lower('ABCDE');

-- upper(str) 返回大写的str字符串
select upper('abcde');

数学相关函数

-- round(n,d) 表示对浮点数进行四舍五入, n 表示小数数字 d表示保留的小数个数，默认d是0
select round(pi(), 2);select round(ifnull(height,0), 1.60) from students;

-- pow(x,y) 表示计算x的y次幂
select pow(2, 3);

-- pi() 表示获取圆周率
select pi();

-- rand() 表示随机生成0-1.0的浮点数
select rand();

时间相关函数

-- current_date() 表示获取当前日期
select current_date();

-- current_time() 表示获取当前时间
select current_time();

-- now() 表示获取当前的日期时间
select now();

-- date_format(date,format) 表示日期格式化
/**	date 表示时间字符串
    format 表示时间格式化字符串	
    %Y：表示完整年份
    %y：表示简写年份
    %m：表示月份
    %d：表示日
    %H：表示24进制的小时数	
    %h：表示12进制的小时数	
    %i：表示分钟	
    %s：表示秒  */
select date_format(now(), '%Y-%m-%d %H:%i:%s');
select date_format('2020-1-1', '%Y');

自定义函数

语法：

delimiter $$
create function 函数名(参数名 参数类型, ...) returns 类型
begin	
    需要执行的sql语句end
$$
delimiter ;

示例代码：

-- 例：自定义去除空格的函数
-- 1. 设置结束分割符号
delimiter $$
-- 2. 定义函数
create function my_trim(value varchar(300)) returns varchar(300)begin 
-- 具体操作的功能代码 
 return trim(value);
end
$$
-- 重新设置回来结束分割符
delimiter ;
-- 3. 调用函数
select my_trim('  aaa   '), char_length(my_trim('  aaa   '));

删除函数：

-- 语法：	
drop function 函数名;

-- 删除函数
drop function my_trim;

视图

  视图好比是一张虚拟表，用于封装复杂的查询sql语句，简化sql查询。
  注意：视图只做查询操作，不能修改。

创建视图语法：

-- 语法：
create view 视图名称[(字段1)(字段2)…] as select 查询语句;

-- 创建视图
create view v_product as select productid,productname,category from products where discontinued=no
-- 以后查询数据只需要通过视图来完成即可
select * from v_product;

删除视图：

drop view v_product;

扩展

三表查询通过视图来完成

-- 创建视图封装三表查询的sql语句
create view query_info as
SELECT	
    gs.id, 
    gs.NAME, 
    gs.price, 
    gs.is_show, 
    gs.is_saleoff, 
    gcs.NAME AS cate_name, 
    gbs.NAME AS brand_name 
FROM 
    goods gs 
    INNER JOIN goods_cates gcs ON gs.cate_id = gcs.id 
    INNER JOIN goods_brands gbs ON gs.brand_id = gbs.id;	

-- 查询视图
select * from query_info;

存储过程

  当需要有多条sql语句一起执行并要提升执行效率的时候，可以通过存储过程来完成，存储过程就是用来提示sql语句的执行效率。

 存储过程的特点:

• 存储过程具有复用性
• 因为存储过程定义好后，SQL语句已经预编译过了，所以执行的速度相对快一些。
• 减少网络之间的数据传输，节省开销

语法：

delimiter $$
create procedure 存储过程名(参数, ...)
begin
    执行的sql
end
$$

示例代码：

-- 创建一个存储过程，目的：提高执行效率，把想要sql语句执行的快放到存储过程中即可。
delimiter $$
create procedure my_proc(age_value int)
begin	
    select * from students where age > age_value;
end
$$
-- 设置结束符
delimiter ;
-- 调用存储过程
call my_proc(18);
-- 删除存储过程
drop procedure my_proc;

索引

  记录数据的位置，提升查询速度。数据库索引好比是一本书的目录，能提升数据库的查询速度。
  提示：主键和外键约束自动添加两个索引

create table goods_price(
  id int unsigned primary key auto_increment not null, 
  name varchar(20) not null, 
  price decimal(10, 2) not null,	
  location varchar(30),
  -- 给name字段添加索引	
  key my_name(name));

-- 表已经存在给字段添加索引
alter table goods_price add index (location);show index from goods_price;

-- 查看索引名
show index from goods_price;show create table goods_price;

-- 删除索引
alter table goods_price drop index location;

索引的优缺点

• 优点：提升查询速度
• 缺点：创建索引会销毁额外的时间和磁盘空间，因为索引是一个文件，需要占用磁盘空间的。

索引使用的原则

1. 频繁查询的字段需要加上索引，比如: name字段。
2. 数据量比较小不需要加索引。
3. 字段当中相同值比较多的，不需要加索引，比如：性别字段。

索引：聚集索引、非聚集索引、唯一索引

 创建索引：

-- 创建聚集索引  #drop_existing:表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为：OFF。 
 create clustered index on students(S_index) with (drop_existing=on)
-- 创建非聚集索引  
create nonclustered index NonClu_index on students(S_index) with (drop_existing=on)
-- 创建唯一索引  
create unique index NonClu_index on students(S_index) with (drop_existing=on)

 修改索引：

-- 语法：  
-- rebuild：表述指定重新生成索引。 
-- disable：表示指定将索引标记为已禁用。  
-- reorganize：表示指定将重新组织的索引叶级。  
alter index NonClu_index on students disable;

 删除和查看索引：

-- 查看指定表中的索引exec 表名;
-- 删除指定表中的索引
-- drop index 表名.索引名
-- alter table 表名 drop index 索引名;
drop index students.Index_Namealter table students drop index Index_Name;

-- 检查表 Student 中索引 UQ_S_StuNo 的碎片信息
dbcc showcontig(students,UQ_S_StuNo)
-- 整理 Test 数据库中表 Student 的索引 UQ_S_StuNo 的碎片
dbcc indexdefrag(Test,students,UQ_S_StuNo)
-- 更新表 Student 中的全部索引的统计信息
update statistics students

修改用户密码

示例代码：

-- 切换到mysql数据库use mysql;
-- mysql 数据库自己带有一个数据库名字是mysql
-- 修改用户密码，需要在mysql数据库下，通过user表进行修改
select * from user;

-- password 是函数，用于加密数据的
select password('mysql');

-- 查看表的字段信息
desc user;

-- 修改root用户密码
update user set authentication_string = password('mysql') where user = 'root';

-- 刷新权限
flush privileges;