MySQL

数据定义语言（DBL）
创建create 创建数据库或数据库对象
修改alter 对数据库或数据库对象进行修改
删除drop 删除数据库或数据库对象

数据操纵语言（date Manipulation Language，DML）
检索select 从表或视图中检索数据
插入insert 将数据插入到表或视图中
修改update 修改表或视图中的数据
删除delete 从表或视图中删除数据

数据控制语言（Data Control Language，DCL）
grant 授予权限
revoke 收回权限

查看表
show columns from mysql_test.customers;
show tables;
desc customers; 查看表结构
SHOW COLUMNS 查看表结构

创建表
mysql> create table test
-> (
-> id int not null auto_increment,
-> name char(50) not null,
-> sex char(1) not null default 0,
-> address char(50) null,
-> contact char(50) null,
-> primary key(id)
-> );

add 更新（向表中添加列）
mysql> alter table mysql_test.test
-> add column city char(10) not null default ‘wuhan’ after sex;

change 更新（修改列名和数据类型）
mysql> alter table mysql_test.test
-> change column sex sex_test char(1) null default ‘M’;

alter 更新（修改指定列的默认值）
mysql> alter table mysql_test.test
-> alter column city set default ‘beijing’;

modify 更新（修改数据类型）
mysql> alter table mysql_test.test
-> modify column name char(20) first;

drop 更新（删除列）
mysql> alter table mysql_test.test
-> drop column contact;

rename to 更新（改表名）
mysql> alter table mysql_test.cutomers_test
-> rename to mysql_test.custermers_test;
rename to 更新
mysql> rename table mysql_test.custermers_test to mysql_test.test;

删除表 drop
查看表 show table
mysql> show tables from mysql_test;
show columns from mysql_test.test;

创建索引
mysql> create table seller
-> (
-> seller_id int not null,
-> seller_name char(50) not null,
-> seller_address char(50) null,
-> seller_contact char(50) null,
-> product_type int(5) null,
-> seles int null,
-> primary key(seller_id,product_type),
-> index index_seller(seles),
-> unique index(seles)
-> );

查看索引
mysql> show index from seller;
mysql> show indexes from mysql_test.seller;

删除索引
mysql> drop index index_seller on mysql_test.seller;

插入数据
使用insert …values语句插入单行或多行元组数据
mysql> insert into mysql_test.test
-> values(‘list’,0,default,‘wuhan’,null);

使用insert set插入部分列数据
mysql> insert into mysql_test.test
-> set name=‘Jason’,address=‘guangzhou’;

使用insert … select 语句插入子查询数据
insert… values 插入单行或多行元组数据

删除数据
使用delete语句删除一行或多行数据
mysql> delete from mysql_test.test
-> where name=‘list’;

修改数据
使用update语句修改更新一个表中的数据
mysql> update mysql_test.test
-> set city=‘hubei’
-> where name=‘Jason’;

查询
查询所有
mysql> select * from mysql_test.test;
查询指定的列
mysql> select name,id,city
-> from mysql_test.test;
查询，使用别名
mysql> select name,address as dizhi
-> from mysql_test.test;

查询判断
mysql> select name,
-> case
-> when sex_test=‘m’ then ‘n’
-> else ‘v’
-> end as xingbie
-> from mysql_test.test;

mysql> select name,id+100
-> from mysql_test.test;
±---------±-------+
| name | id+100 |
±---------±-------+
| NULL | 101 |
| zhangsan | 1001 |
| Jason | 1003 |
±---------±-------+

mysql> select * from tb_stu inner join tb_score
-> on tb_stu.sno=tb_score.sno;

mysql> select * from tb1 cross join tb2;
mysql> SELECT * from tb1,tb2;

where 子句与条件查询（范围查询）
mysql> select * from mysql_test.tb_score
-> where sno between 101 and 103;

in枚举
mysql> select * from mysql_test.tb_score
-> where sno in(101,102,103);

is判断空值
mysql> select * from test
-> where address is null;

limit子句
使用limit子句限制被select语句返回的行数

创建视图
使用create view创建视图
mysql> create view mysql_test.test_view
-> as
-> select * from mysql_test.test
-> where sex_test=‘M’
-> with check option;

删除视图
使用drop view语句删除视图
drop view [if exists]
view_name
mysql> delete from mysql_test.test_view
-> where name=‘li’;

使用alter view语句对已有视图的结构进行修改
alter view view_name
as select_statement

mysql> alter view mysql_test.test_view
-> as
-> select * from mysql_test.test
-> where sex_test=‘f’
-> with check option;

使用show create view 语句查看已有视图的定义（结构）
show create view view_name
mysql> show create view mysql_test.test_view;

查看视图
mysql> select * from mysql_test.test_view;

mysql> select name,address
-> from mysql_test.test_view
-> where id=901;

插入视图
mysql> insert into mysql_test.test_view
-> values(‘luxi’,‘2’,‘f’,‘hannan’,null);

修改视图
mysql> update mysql_test.test_view
-> set city=‘shenzhen’
-> where name=‘li’;

存储过程
delimiter命令
delimiter $$ 用户定义的结束符

创建存储过程
使用create procedure 语句创建存储过程
create procedure sp_name routine_body

创建存储函数
使用create function语句创建存储函数
create function sp_name
returns type
routine_body

mysql> delimiter $$mysql>createfunctionfnh{s}_{s}earch(cidint)->returnschar(20)->deterministic->begin->declaresschar(20);->selectse{x}_{t}estintossfromtest->whereid=cid;->ifssisnullthen->return(selec{t}^{\prime }meiyo{u}^{\prime });->elseifss{=}^{\prime }{f}^{\prime }then->return(selec{t}^{\prime }n{v}^{\prime });->elsereturn(selec{t}^{\prime }ma{n}^{\prime });->endif;->endif;->end$$

mysql> select fnhs_search(903)$$

创建触发器：
mysql>create trigger mysql_test.test_insert_trigger after insert
-> on mysql_test.test for each row set @str=‘one test added’;

drop trigger mysql_test.test_insert_trigger;

查看密码
mysql> select password(123);
±------------------------------------------+
| password(123) |
±------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
±------------------------------------------+

创建用户
mysql> create user ‘Jessica’@‘localhost’ identified by ‘123’,
-> ‘Jason’@‘localhost’ identified by password ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257’;

查看用户
mysql> select user from mysql.user;

删除用户
mysql> drop user jin@localhost;

修改用户名
mysql> rename user ‘wangwu’@‘localhost’ to ‘James’@‘localhost’;

账号权限管理
mysql> grant select(id,name)
-> on mysql_test.test
-> to ‘Jessica’@‘localhost’;
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,update
-> on mysql_test.test
-> to ‘lining’@‘localhost’ identified by ‘123’,
-> ‘hamburger’@‘localhost’ identified by ‘678’;

mysql> grant all
-> on mysql_test.*
-> to ‘hamburger’@‘localhost’;

mysql> grant create user
-> on .
-> to ‘hamburger’@‘localhost’;

mysql> grant create user
-> on .
-> to ‘hamburger’@‘localhost’
-> with grant option;

mysql> revoke select
-> on mysql_test.test
-> from ‘hamburger’@‘localhost’;

备份数据的方法
使用select into … outfile语句备份数据
select
恢复数据select