MYSQL查询语句的经典操作图.docx

1、MYSQL查询语句的经典操作图数据库SQL语句的操作1. DDL alter drop creat针对于结构2. DCL grant commit roolback针对于权限、事务处理3. DML select update insert delect 针对于具体的增删改查create table 新表名 as select * from 原表; 备份表alter table 表名 constraint PK_ID primary key (表中字段（属性）); 为表添加主键创建表 删除数据库 为列属性改名 增加列属性 更改列属性的数据类型 复制表mysql show databases; 显

2、示所有数据库+-+| Database |+-+| information_schema | mysql | photoupload | test |+-+4 rows in set (0.00 sec)mysql use test; 选择数据库Database changedmysql show tables;+-+| Tables_in_photoupload |+-+| photos | student | student2 |+-+3 rows in set (0.00 sec)mysql alter table student add constraint PK_ID primary

3、 key (id); 为数据库添加主键Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql desc student; 显示表信息+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| id | int(11) | NO | PRI | 0 | | name | char(90) | YES | | NULL | |+-+-+-+-+-+-+2 rows in set (0.00 sec)mysql

4、alter table student add column mark float(45); 为表添加列属性Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql desc student; 显示表+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| id | int(11) | NO | PRI | 0 | | name | char(90) | YES | | NULL | | mark | do

5、uble | YES | | NULL | |+-+-+-+-+-+-+3 rows in set (0.00 sec)mysql alter table student add column age int; 为表添加列属性Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql insert into student(id,name,age,mark) values(1,王明,19,99.5); 添加数据ERROR 1366 (HY000): Incorrect string value: x

6、CDxF5xC3xF7 for column name at row 1 中文乱码mysql 更改字符编码mysql select * from student; 更改编码后+-+-+-+-+| id | name | mark | age |+-+-+-+-+| 1 | 王明 | 99.5 | 19 | 2 | hao | 99.5 | 22 |+-+-+-+-+2 rows in set (0.00 sec)mysql alter table student modify id int auto_increment; 设置主键自动增长Query OK, 2 rows affected (0

7、.13 sec)Records: 2 Duplicates: 0 Warnings: 0mysql alter table student alter column sex set default 男; 设置属性约束（sex=男）Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0 设置默认属性值后，插入数据 修改update student set sex=男 where sex is null; 修改属性值update student set sex=男 where sex =nan; mysql

8、update student set sex=女 where id%2=1; 修改属性值Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0查询 模糊查询 “_”代表单个字符匹配 “%”代表多个字符匹配 “or”查询 “in”查询 “between and ”查询聚合函数聚合函数count 统计 省略“as”为统计起别名 “as”添加统计别名“avg”平均数“max”最大值“min”最小值“sum”求和子查询Group by查询“DESC”降序排序“ASC”升序排序（默认升序，asc可省）Having查询添加约束“Check”检查约束插入数据唯一约束外键约束添加外键约束后的删除复制表 复制表结构