一、数据库操作
1.连接数据库:mysql -u root -p
2.查看已经有的数据库:show databases;
3.新建数据库:create database database_name;
4.删除数据库:drop database database_name;
5.选择数据库:use database_name;
二、表操作
1.查询所有的表:show tables;
2.创建表:create table table_name(需要字段);
3.删除表 :drop table table_name;
4. 查看表结构:desc table_name;
5.查看建表语句:show create table table_name;
6.修改表名 rename :alter table old_table_name rename new_table_name;
7.修改字段名和属性 change:alter table table_name change old_table_name new_table_name;
8.添加字段 add:alter table table_name add new_field attr;
9.修改字段顺序 modify:alter table table_name modify field1 attr1 [after field2|first];
10.删除字段 drop:alter table table_name drop field;
三、数据存取
1.插入数据:insert into table_name (field1,field2,.....) value(value1,value2,value3....);
2.查询数据:select [field1,field2|*] from table_name [where condition];
3.修改数据:update table_name set field1=value1,field2=value2 where condition;
4.删除数据:delete from table_name [where condition];
5.备份和还原:备份 mysqldump -u root -p database_name [table_name] > file
还原 mysql -u root -p database_name < file
6.where 条件:=, !=, > ,<, >=, <=, and, or, in, not in, between a and b
四、数据约束
1.唯一性约束 unique key:alter table userinfo change idCard idCard char(18) unique;
2.主键 primary key 自带唯一属性;
3.自增 auto_increment:alter table table_name change field field attr auto_increment;
4.非空约束 not null:alter table table_name change field field attr not null;
5.默认值 default : alert table table_name change field field attr not null default 'def_value';
6.设置注释 comment ;
7.外键 foreign key:alter table table_name add constraint 外键名 foreign key(field) references 主表名(field) [on delete cascade on update cascade]
on delete cascade //主表删除的时候子表跟着删除
on update cascade //主表更新 子表更新
on delete set null // 主表删 子表设空
五、数据查询
A、单表
1.查询所有列:select * from table_name [where condition];
2.指定查询某一列或多列:select field_name[,field_name2....] from table_name
3.去除重复项 distinct:select distinct phone,class from students;
4.运算 +-*/:select name,score/age as score_age from table_name
5.空值判断 is null:select * from students where phone is null
6.排序 order by:order by field [asc|desc] [,field2 [asc|desc]] ||select * from students order by score desc
7.模糊查询 like:% 代表任意个字符
_ 代表一个任意字符
select * from students where phone like '159%';
8.数量限制 limit:limit n; 从第1行(下标为0)开始取 ,取n条
limit m,n; 从第m条(下标从0开始)取,取n条
select * from students order by score desc limit 0,2;
9.统计函数:
avg(field) 平均值
min(field) 最小值
max(field) 最大值
sum(field) 求和
count(field) 统计条数
10.分组查询 group by:select class,sum(field) from students group by field;
11.分组后条件限制 having:select class,avg(score) as ascore from students group by class having ascore >=60
B、多表
1.连接查询:select field from table1 as t1 inner join table2 as t2 on t1.field=t2.field
2.子查询:select * from students where id in (select s_id from score where score<60 and subject ='语文')
六、索引+存储过程+触发器+事务
索引:http://www.runoob.com/mysql/mysql-index.html
存储过程:
-- delimiter //
-- create procedure test_out(IN s_name char(5),OUT s_age int)
-- BEGIN
-- select age into s_age from students where name = s_name;
-- into 把得到的结果赋值给s_age
-- end//
-- delimiter ;
-- set @age=0; //定义用户变量
-- call test_out('小橙',@age); //使用@age变量接受 out的s_age
-- select @age; //输出变量
触发器
-- create TRIGGER update_count after update on students for each ROW
-- BEGIN
-- update class set number=number+1 where id= new.class;
-- update class set number=number-1 where id= old.class;
-- end;
-- update students set class='102' where id=2007;
事务:【转载】https://www.cnblogs.com/jiangxiaobo/p/7728764.html