mysql数据库中的SQL语句

一、mysql数据库的基本操作

注意：在数据库中每条语句结束都是以;号结尾。

1、连接数据库

mysql -u用户名 -密码;

mysql -uroot -p;------------>然后输入密码

2、退出数据库

quit/exit/ctrl+d

3、显示数据库版本

select version();

4、显示时间

select now();

二、数据库与表的操作(增/删/改/查)

数据库中的数据类型

数值类型(常用)

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

字符串

类型	字节大小	示例
CHAR	0-255	类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’
VARCHAR	0-255	类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’
TEXT	0-65535	大文本

日期时间类型

类型	字节大小	示例
DATE	4	‘2020-01-01’
TIME	3	‘12:29:59’
DATETIME	8	‘2020-01-01 12:29:59’
YEAR	1	‘2017’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

1、查看所有数据库与当前数据库中的所有表

show databases;

show tables;

2、查看当前使用的数据库与设置当前数据库

select databases;

use 数据库名;

3、查看创建数据库与创建表的SQL语句

show create database 数据库名;

show create table 表名;

4、创建数据库与创建表

create database 数据库名 charset=utf8;

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

约束：

auto_increment-------->表示自动增长(从1开始)

not null------------------->表示不能为空

primary key------------->表示主键

default 值--------------->表示默认值，值可以是字符，数字，枚举类型

5、删除数据库与删除表

drop database 数据库名;

drop table 表名;

6、查看表结构

desc 表名;

7、表字段的增、删、改

alter table 表名 add 列名 类型;

alter table 表名 drop 列名;

alter table 表名 modify 列名 类型及约束;---------->改变表中已存在列名的类型及约束

alter table 表名 change 原名 新名 类型及约束;

8、插入表记录

(1)插入部分记录

insert into 表名(列1,列2…) values(值1,值2…);

(2)插入多行记录(全列插入：必须保证插入的值数对应所有的列数)

insert into 表名 values(值1,值2,值3,…),(值1,值2,值3,…),…;

注意：主键字段 可以用 0 null default 来占位

9、修改表记录(根据列修改记录)

update 表名 set 列1=值1，列2=值2,… where 条件;

注意：没有where 条件;则是全部修改

10、表记录的物理删除与逻辑删除

delete from 表名 where 条件;

alter table 表名 add is_delete bit default 0;

11、查询记录

(1)一般查询
1)、select *或列名 from 表名 where 条件;
注：
*号表示所有列，也可以指定查询的列
条件为空表示查询所有记录

2)、查询的结果以别名显示列(不改变物理列)
select 字段1 as 别名1,字段2 as 别名2 from 表名 where 条件;

3）、消除重复行
select distinct 字段1 from 表名;

（2）条件查询
1）、比较运算符(>,>=,<,<=,=,!=或<>)

-- 查询大于18岁的信息
	select * from students where age>18;
	
-- 查询小于18岁的信息
	select * from students where age<18;

-- 查询小于或者等于18岁的信息
	select * from students  where  age<=18;
	
-- 查询年龄为18岁的所有学生的名字
	select  name  from students where age=18;
--查询年龄不为18岁的所有学生的名字
	select  name  from  students   where  age != 18;

2）、逻辑运算符(and,or,not)

-- 18到28之间的所以学生信息
	select * from students where age>18 and age<28;
	
-- 18岁以上的女性
	select * from students where age>18 and gender="女";
	select * from students where age>18 and gender=2;
	
-- 18以上或者身高超过180(包含)以上
	select * from students where age>18 or height>=180;

-- 不在 18岁以上的女性 这个范围内的信息
	select * from students where not age>18 and gender=2;
	select * from students where (age>18 and gender=2);
	
-- 年龄不是小于或者等于18    并且是女性
	select * from students where (not age<=18) and gender=2;

（3）模糊查询 like(%：替换一个或多个，_：替换一个) 和 rlike :正则

-- 查询姓名中 以 "小" 开始的名字
	select name from students where name="小";
	select name from students where name like "小%";

-- 查询姓名中 有 "小" 所有的名字
	select name from students where name like "%小%";

-- 查询有2个字的名字
	select name from students where name like "__";

-- 查询有3个字的名字
	select name from students where name like "___";

-- 查询至少有2个字的名字
	select name from students where name like "__%";

-- rlike 正则
-- 查询以 周开始的姓名
	select name from students where name rlike "^周.*";
		
-- 查询以 周开始、伦结尾的姓名
	select name from students where name rlike "^周.*伦$";

（4）范围查询(in,not in,between … and …)

-- in (1, 3, 8)表示在一个非连续的范围内
	-- 查询 年龄为18、12、34的姓名
		select name,age from students where age=18 or age=34;
		select name,age from students where age=18 or age=34 or age=12;
		select name,age from students where age in (12,18,34);
		
-- not in 不非连续的范围之内
	-- 年龄不是 18、12、34岁之间的信息
		select name,age from students where age not in (12,18,34);
		
-- between ... and ...表示在一个连续的范围内
	-- 查询 年龄在18到34之间的的信息
		select name,age from students where age between 18 and 34;
		
-- not between ... and ...表示不在一个连续的范围内
	-- 查询 年龄不在在18到34之间的的信息
		select * from students where age not between 18 and 34;
		select * from students where not (age between 18 and 34);

（5）判断空(null)

-- 判空is null
	-- 查询身高为空的信息
		select * from students where height is null;
		select * from students where height is Null;
		select * from students where height is NULL;

-- 判非空is not null
	-- 查询身高不为空的信息
		select * from students where height is not null;

（6）排序(order by )

-- order by 字段
	-- asc从小到大排列，即升序
	-- desc从大到小排序，即降序

-- 查询年龄在18到34岁之间的男性，按照年龄从小到大排序
	select * from students where (age between 18 and 34) and gender=1 order by age;
	
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序
	select * from students where (age between 18 and 34) and gender=2 order by height desc;
	
-- order by 多个字段
	-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
	select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc;

（7）聚合函数

-- 总数 count
	-- 查询男性有多少人，女性有多少人
	select count(*) as 男性人数 from students where gender=1;
	select count(*) as 女性人数 from students where gender=2;
	
-- 最大值 max
	-- 查询最大的年龄
	select max(age) from students;

-- 最小值  min
	-- 查询最小的年龄
	select min(age) from students;

	
-- 求和  sum
	-- 计算所有人的年龄总和
	select sum(age) from students;

-- 平均值  avg
	-- 计算平均年龄
	select avg(age) from students;
	-- 计算平均年龄 sum(age)/count(*)
	select sum(age)/count(*) from students;

-- 四舍五入 round(123.23 , 1) 保留1位小数
	-- 计算所有人的平均年龄，保留2位小数
	select round(sum(age)/count(*),2) from students;
	-- 计算男性的平均身高 保留2位小数
	select round(avg(height),2) from students where gender=1;

（8）分组（group by）

-- 按照性别分组,查询所有的性别
	select gender from students group by gender;
	
-- 计算每种性别中的人数
	select gender,count(*) from students group by gender;

-- 计算男性的人数
	select gender,count(*) from students where gender=1 group by gender;
	
-- group_concat(字段1，字段2，....)
	-- 查询同种性别中的姓名，性别，id
	select gender,group_concat(name,age,id) from students where gender=1 group by gender;
	select gender,group_concat(name,"_",age," ",id) from students where gender=1 group by gender;
	
-- having--对分组进行条件判断
	-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
	select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;
	
	-- 查询每种性别中的人数多于2个的信息
	select gender,group_concat(name) from students group by gender having count(*)>2;