DDL:数据定义语言,用于修改数据库、表的结构,关键字create,drop,alter
登录MSY服务器:mysql -u root -p
创建数据库:create database dbname;
删除数据库:drop database dbname;
查看系统所有数据库:show databases;
选择需要操作的数据库:use dbname;
查看选中数据库中所有的表:show tables;
创建表:create table tablename(column_name_1 column_type_1 constraints1);
删除表:drop table tablename;
查看表定义:desc tablename;
修改表:
修改字段类型:alter table tablename modify [column] column_definition [first | after col_name]
增加表字段:alter table tablename add [column] column_definition[first_after col_name]
删除表字段:alter table tablename drop col_name;
字段改名:alter table tablename change [column] old_column_name new_column_definition [first|after col_name]
更改表名:alter table tablename rename [to] new_name
DML:数据操作语言,对记录的操作,关键字insert,delect,update,select
插入记录:insert into tablename (field1,field2,....) values (values1,value2......),(values1,value2......) //插入记录
更新记录:update tablename set field1=value1,field2=value2,,,,fieldn=valuen [ where condition ];//更新一个表中记录
update tablename1,tablename2 set tablename1.field=expr1 , tablename2.field=expr2,,,, [ where condtion ];//更新多个表中的记录
删除记录:delete from tablename [ where condition ]; //删除一个表中记录(不加where条件则删除全部记录)
查询记录:select field1,field2,,,, from tablename [ where condition ]; //普通查询
查询不重复的记录:select distinct field,field2,,,, from tablename; //针对field1,field2查询后的结果去掉重复项
排序和限制:select field1 , field2,,,from tablename [ where condition ] [ order by field1 [ asc | desc ] [ limit offset_start,row_count];
聚合:select function_name from tablename //聚合操作
[ where where_condition ] //是否对聚合前的结果进行条件过滤
[ group by field1,field2,,,] //进行聚合的字段
[ with rollup ] //是否对聚合后的结果进行再汇总
[ having where_condition ] //是否对聚合后的结果进行条件过滤
表连接:同时显示多个表中的字段
内连接:select tablename1.field , tablename2.field from tablename1,tablename2 where condition;
外连接(左连接):select tablename1.field,tablename2.field from tablename1 left join tablename2 on where_condition
外连接(右连接):select tablename1.field , tablename2.field from tablename1 right join tablename2on where_condition
子查询:select field1,field2 from tablename1 where field in (select field from tableanme2)
记录联合:将多表的结果合并到一起显示。
select * from t1 union/union all(uinon合并无重复的)
select * from t2
......
union/union all
select from tn;
DCL:管理系统中用户的权限,关键字grant,revoke.
授权:grant priviledge on schemaname.tablename to username identified by passwd;
收回:revoke priviledge on schemaname.tablename from username@passwd;