mysql阶段总结

title: 流弊的mysql(08day)
tags: database
notebook: Database

mysql总结:database篇

#:代表错误对比

1. create database data_name charset utf8; 创建database并设置字符集
2. create database data_name charact set=utf8;　
   create database if not exists data_name charset utf8;
3. show create database data_name; 查看database参数
4. #show database status; 查看整个数据库的参数状态;
5. drop database data_name;　删除database
   #drop database if not exists data_name;
6. alter database data_name charset utf8; 修改database字符集
7. #alter database if not exists data_name charact utf8;

mysql总结:table篇

1. create table table_name(
id int(50) not null auto_increment primary key comment '身份证号',
name varchar(100) not null comment '姓名',
birthday　date not null comment '出生日期'
) comment '表明'; 创建table
    show create table table_name; 查看table参数
    show table status \G; 查看全部tables参数
2. alter table table_name convert to charset utf8; 修改表格字符集
3. rename table old_table_name to new_table_name;　修改table名称
4. drop table table_name; 删除table

table字段篇

1. alter table table_name add 字段; 增加字段
2. alter table table_name modify 字段; 该字段用的是旧名，设置新的格式 修改字段
3. alter table table_name change old_column_name           new_column_name 字段格式; 修改字段名称
4. alter table table_name drop column column_name; 删除字段

table数据篇

1. insert into table_name (id,name) values (23,'r3r3'),(232,'sisi'); 添加数据
2. updata table_name set column_name='haha' where id=23;　修改数据
3. delete from table_name where id=32; id:标示要删的行数据
4. truncate table_name; 删除表格，重建列表

table查询篇

1. select * from table_name where id=23; 依据id选择全部信息
2. select id from data_name.table_name where id=43;
3. select * from table_name where id=43 and name='haha';#and左右链接的两个条件同时满足
4. select * from table_name where id=43 or name='haha';#or左右两个条件都作为要挑选的分段，即满足左条件select数据、满足右条件select数据，select出来的两种数据并存不冲突
 select name,age from table_name where (id=32 or id=43) and age >= 23;
5. select name,age from table_name order by age desc,name; 按年龄降序选择name,age
6. select name,age from table_name order by age asc;按升序选择name,age
7. select name.age from table_name where age=32 order by name; 选择年龄为32按name排序选择name,age
8. select name,age from table_name where age!=19 and age!=23; 选择年龄既!=19也!=23数据name,age
9. #select name,age from table_name where age!=19 or age!=23; 该语句不起作用
10. select name,age from table_name where age not in (18,21,22); 年龄不在(18,21,22)选择name,age
11. #select name,age from table_name where height=null;
12. select name,age from table_name where height is null; 选择height为null的数据name,age
13. select name,age from table_name where birthday between '1990/2/2' and '2000/3/2'; 选择birthday在'1990/2/2'～'2000/3/2'范围的数据name,age
14. select name,age from table_name order by age limit 3,4; 按年龄排序索引为3包括本行后4行　注:数据索引从0开始
15. select name from table_name order by age limit 4 offset 3; offset偏移量为４，从索引为３开始