Mysql 基础知识总结

命令行操作

1.链接mysql: mysql -uroot -p123456 ;  注释: -u后跟是用户名, -p密码
2. 创建数据库: create  database  库名字;
3. 查询所有数据库: show databases;
4. 进入某个数据库: use  库名字;
5. 查看某个数据库中的表: show tables;

创建数据表

 1创建数据库
  create  database  库名字;
2.创建数据表
create table if not exists stu(id int not null primary  key  auto_increment , name varchar(20), age int )

数据表插入数据

1.第一中插入方式
insert into stu values('zhangsan', 20)
2.第二种插入方式
insert into stu (name, age) values('zhangsan', 20)

数据表数据更新

1.修改某一条数据
update stu set age=30 where  name='zhangsan';
2.修改全部数据
update stu set age=10;

数据删除操作

1.删除数据表中的一条数据
	delete from stu where name='xxy';
2. 删除数据表中所有数据
	delete from stu;
3.删除数据表
	drop table 数据表名
4.删除数据库
	drop database 数据库名
5.清空数据表的数据
	truncate table stu;

alter 相关操作

1.修改数据表名
	alter table stu rename student
2.添加一列
	aletr table stu add phone int;(字段和数据类型)
3. 数据表中添加多个列
	alter table stu add email varchar(20), class varchar(30);
4.指定在某列后面或前面添加一列
	alter table stu add  habbit varchar(20) first/after age;
5.删除数据表中某列
 	alter table stu drop phone;(phone: 列名字)
 6.删除数据表的主键
 	alter table stu drop primary key
 7.修改某一列为主键
 	alter table stu add primary key(name);
 8.修改某个字段的数据类型
 	alter table stu modify age varchar(20);
 9. 设置某个字段为唯一
 	alter table stu add unique(name);
  10: 设置某个字段为自增的主键
  	alter table stu modify id int not null primary key auto_increment;
  	

数据表查询

1.查询所有数据
	select * from stu;
2.根据某个条件查询数据
	select  * from stu where name='zhangsan'
3. 去重查询的数据
	select distinct name from stu;
4.条件查询区间查询
	select * from stu where age >20 and age<50;
	select * from stu  where age between 20 and 50;
5.数据查询排序: order by 默认是asc: 升 序, desc: 降序
  	select * from stu order by age desc;
6.模糊查询:%是占位符
    select * from stu where name like '%三%';
4. 子查询: 执行顺序是先获取age的数据, 然后在根据age所在区间进行查询
     select * from stu where age in (select age from stu where age >20 and age <30);
8.多表查询
	 select stu_name, avg(degree) from student , score where student.stu_num=score.stu_num group by student.stu_name;
9.联表查询, join / left join /right join
	select stu_name, avg(degree) from student join score on 	student.stu_num = score.stu_num;
5. group by order by 同时运用时, group by 是在order by 前面

利用正则进行搜索

正则表达式可以直接利用
# 匹配 sname 字段中包含 4 的数据
select * from student  where sname regexp '4';
#正则表达式匹配： sname 字段中含有数据 [0-9], 匹配数据一次、多次
select * from student  where sname regexp '[0-9]+';
# 查询sno 是4， 或者6 的数据
select * from student  where sno regexp '4|6';

查询字段拼接显示：Concat()

# 输出会把： sname, ssex 拼接在一起输出
select  concat(sname, '性别', ssex)  from student ;
输出结果：
+-------------------------------+
| concat(sname, '性别', ssex)   |
+-------------------------------+
| zhang4性别m                   |
| li4性别m                      |
| wang5性别f                    |
| zh4性别m                      |
| zhao4性别m                    |
| ma6性别f                      |
+-------------------------------+

sql查询利用函数

# 主要处理函数有： Left, Right, Lower, Upper, SubSting等文本处理函数
# 把sname 字母转成大写
select Upper(sname) from student;
+--------------+
| Upper(sname) |
+--------------+
| ZHANG4       |
| LI4          |
| WANG5        |
| ZH4          |
| ZHAO4        |
| MA6          |
+--------------+
# 获取某个字段的长度
select Length(sname) from student;
+---------------+
| Length(sname) |
+---------------+
|             6 |
|             3 |
|             5 |
|             3 |
|             5 |
|             3 |
+---------------+
# Date(): 处理时间, 时间处理函数： Month, Year， Hour, Minute, Now, Time等
select oredr_id, order_name from orders where Date(order_date) = "2020-02-24"

sql聚合函数

# 主要函数有： AVG(平均值), MIN(最小值), MAX(最大值), SUM(和),COUNT(统计个数)
# 学号分组查询总成绩
select sno, SUM(score) from sc group by sno;
+-----+------------+
| sno | SUM(score) |
+-----+------------+
|   1 |        139 |
|   2 |        190 |
|   3 |        139 |
|   4 |        239 |
|   5 |         40 |
|   6 |        166 |
|   7 |        149 |
|   8 |         70 |
|   9 |         80 |
|  10 |         96 |
+-----+------------+
# 查询平均值
 select sno, AVG(score) from sc group by sno;
+-----+------------+
| sno | AVG(score) |
+-----+------------+
|   1 |    69.5000 |
|   2 |    95.0000 |
|   3 |    69.5000 |
|   4 |    79.6667 |
|   5 |    40.0000 |
|   6 |    83.0000 |
|   7 |    74.5000 |
|   8 |    70.0000 |
|   9 |    80.0000 |
|  10 |    96.0000 |
+-----+------------+

sql分组group by， having, order by

# 分组， 并把平均分大于 70的数据筛选出来
select sno,AVG(score) as avg from sc group by sno HAVING avg > 70;
+-----+---------+
| sno | avg     |
+-----+---------+
|   2 | 95.0000 |
|   4 | 79.6667 |
|   6 | 83.0000 |
|   7 | 74.5000 |
|   9 | 80.0000 |
|  10 | 96.0000 |
+-----+---------+
# 带有数据表字段筛选， 然后在进行分组， 排序过滤
select sno,AVG(score) as avg from sc where sno >2 group by sno HAVING avg > 70;
+-----+---------+
| sno | avg     |
+-----+---------+
|   4 | 79.6667 |
|   6 | 83.0000 |
|   7 | 74.5000 |
|   9 | 80.0000 |
|  10 | 96.0000 |
+-----+---------+
# 带有排序
select sno,AVG(score) as avg from sc where sno >2 group by sno HAVING avg > 70 order by avg DESC;
+-----+---------+
| sno | avg     |
+-----+---------+
|  10 | 96.0000 |
|   6 | 83.0000 |
|   9 | 80.0000 |
|   4 | 79.6667 |
|   7 | 74.5000 |
+-----+---------+

sql语句子查询， union

# 子查询
select sname, sno, (select AVG(score) from sc where sno =  student.sno) as avg from student;
+--------+------+---------+
| sname  | sno  | avg     |
+--------+------+---------+
| zhang4 |    2 | 95.0000 |
| li4    |    3 | 69.5000 |
| wang5  |    4 | 79.6667 |
| zh4    |    5 | 40.0000 |
| zhao4  |    6 | 83.0000 |
| ma6    |    7 | 74.5000 |
+--------+------+---------+
# union
select sname from student where sname like '%w%' union select sname from student where sname like '%zh%';

sql语句联表查询： join, left join, right join

# join 查询
 select sname,  AVG(score) from student join sc on student.sno =sc.sno group by sc.sno;
+--------+------------+
| sname  | AVG(score) |
+--------+------------+
| zhang4 |    95.0000 |
| li4    |    69.5000 |
| wang5  |    79.6667 |
| zh4    |    40.0000 |
| zhao4  |    83.0000 |
| ma6    |    74.5000 |
+--------+------------+