说说数据库的那些个操作

2 netstat -an|find "3306"      检测MySQL是否启动,3306是默认端口号,tcp监听,未开启则此命令没反应     
3 mysql -uroot -p      进入MySQL,-u用户名,-p密码     
4 use mysql;      改密码     
5 update user password = password("123456")where user = "root";           
6 select version();      查看当前MySQL版本号     
7  select now();      查看当前时间     
8 select 1+1;      1+1运算     
9  create database python01 charset = utf8;      创建一个名为python01的库+申明     
10 show databases;      列出所有数据库     
11  drop database python01;      删除名为python01的库，=rm不可逆     
12 exit、quit      退出     
13 use python01;      进入名为python01的库     
14 show tables;      打印当前数据库里所有的表     
15 desc classes;      打印表名为classes的字段     
16 select * from classes;      打印表内数据     
17 create table classes1(id int primary key auto_increment not null,name varchar(30),age int default‘10‘);      创建表(表名为classes1,id为整形,主键(永远不可以改变,重复报错),自增长(只能有一个字段),不能为空,名字,字符长度<30,年龄,整形,默认10)     
18 insert into classes1 values(0,‘lu‘,null)      插入数据(id位的0是占位,name:lu,age:null为默认     
19 select name from classes1;      打印classes1表格里name栏     
20 update classes1 set name = ‘123‘ where id = 1;      更改classes1表格里id = 1 的name值为123     
21 delete from classes1 where id = 1;      删除一条数据,没有where语句删除表格     
22 drop table classes1;      删表     
23 select name as nickname from classes;      用nickname(别名)代替name      as只能用于字段,跟数据无关     
24 select distinct(去重) num from classes where mun is null;      将classes表格里num栏里为null的去重     
25 select * from classes where id <= 3;      打印classes表格里id<=3的数据  (<>、!=)除了整形还可以用于datetime     
26 select * from classes where num is(not) null;      打印classes表格里num栏是(否)为null       is 和 not 只能用来判断null   优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符 and比or先运算，如果同时出现并希望先算or，需要结合()使用  
27 select * from student where add_time between ‘2018-05-08‘ and ‘2018-05-10 09:00:00‘;      打印student表格里add_time栏里时间在两者之间的数据,between and相当于>=、<=可作用于时间和整形     
28  select name from student where id > 1 and id < 3;      打印student表格里id>1并<3的name栏   and or not 逻辑运算符     
29  select * from student where name like ‘小%‘;      模糊查找 like 打印student表格里姓名栏(字段)里以‘小‘开头的数据     
30  select * from student where name like ‘_明‘;      占位匹配  _  一个下划线代表一个字符     
31  select * from student where id (not) in (1,2,3);      打印student表格里id是(否)在(1,2,3)里的数据，是否在范围内     
32  select * from student order by id desc,height asc;      排序,mysql默认根据主键正序排列,order by 排序字段,asc正序,desc倒序,防止一个条件相同内容导致无法排序,再增加第二个排序条件进行排序     
33select count(*) from student;      打印student表里的总个数，总人数   聚合函数  
34  select count(*) from student where gender = ‘女‘;      打印student表格里女生的人数    count 
35  select max(age) from student;      打印student表格里最大的年龄值    max 
36  select min(age) from student where gender = ‘女‘;      打印student表格里女生的最小年龄值    min 
37  select sum(age) from student;      打印student表格里年龄累加的和    sum 
38  select avg(age) from student;      打印student表格里平均年龄,显示浮点数    avg 
39 select count(*),gender from student group by gender;      group by分组,以gender分组,select与from之间为打印出来的内容,分别统计男、女人数     
40  select count(*),age from student where age = 20 group by age;      打印student表格里年龄为20的人数     
41  select count(*),age,group_concat(id) from student where age = 20 group by age;      打印student表格里年龄为20的人数,和他们的id,group by+聚合函数count使用,否则与distinct一样是去重     
42 select gender,count(*) from student group by gender having count(*) > 3;      student表格里以gender分组,打印gender与count个数,having必须跟group by使用,是在分组后二次筛选     
43 select * from student limit 1,2;      limit限制 1:从第二个开始,取2个数据     
44 select * from student order by rand() limit 1,2;      order by rand()随机取数据     
45  select gender,count(*) from student group by gender with rollup;      以gender分组,gender和count,   with rollup累加count      
46 select student.id,student.name,classes.name from student inner join classes on student.cls_id = classes.id;      inner join内连 left join左连 right join右连     表名join 表名 on 连接字段   一对多  
47 select student.name,course_student.cid from student left join course_student on student.id = course_student.sid;      以student.id与course_student.sid连接匹配,打印student.name,course_student.cid   多对多，2个一对多  
48 alter table 表名 rename to 新表名      修改表名     
49 ALTER TABLE `student` ADD COLUMN `num1` int(3) NULL DEFAULT NULL;      新增字段     
50 ALTER TABLE `classes` CHANGE COLUMN `num1` `num2`  int(3) NULL DEFAULT NULL;      修改字段     
51 ALTER TABLE `classes` DROP COLUMN `num`;      删除字段     
52          三表(2明细表+1关系表)相连取交集打印学生名字与课程名字   三表连查  
53  select * from areas as p inner join areas as a on p.aid = a.pid where p.atitle = ‘河南省‘;      省、市，两表查询在一张表里(as起别名)用上级id打印，   自关联  
54 select * from areas as p inner join areas as c on c.pid = p.aid inner join areas as a on a.pid = c.aid where a.atitle = ‘二七区‘;      省、市、区，三表查询在一张表里(as起别名)用上级id打印区表title为‘二七区’的数据     
55  create view v_areas as select * from areas;      view视图,建立在表基础之上,as定界符(as前固定格式,as后是表数据)，将areas表格创建为视图v_areas     
56  select * from v_areas;      查看视图数据     
57 create or replace view v_areas as select * from student;      改 将v_areas视图里的数据替换为student表格里的数据     
58  drop view v_areas;      删除视图v_areas     
59 select * from student where gender = ‘男‘ and height >(select avg(height) from student);      列出身高大于平均身高的数据   标量子查询:返回定值,数字、字符串   标量子查询  
60 select * from student where cls_id in (select id from classes where id in (1,2));      列出学生报课程id为在(1,2)范围内的数据  列子查询(一列多行)用in范围查找   列子查询  
61  select * from student where (age,height) = (select max(age),max(height) from student);      列出表中年龄最大,身高最高的人  行子查询(一行多列)   行子查询 

说说数据库的那些个操作

标签：count   插入数据   关联   判断   lte   查看   排序   否则   逻辑