title: 漂亮的mysql
tags: database
notebook: Database
mysql總結(jié):database篇
#:代表錯(cuò)誤對比
1. create database data_name charset utf8;
2. create database data_name charact set=utf8;
create database if not exists data_name charset utf8;
3. show create database data_name;
4. #show database status; 查看整個(gè)數(shù)據(jù)庫的參數(shù)狀態(tài);
5. drop database data_name;
#drop database if not exists data_name;
6. alter database data_name charset utf8;
7. #alter database if not exists data_name charact utf8;
mysql總結(jié):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 '表明';
2. alter table table_name convert to charset utf8;
3. rename table old_table_name to new_table_name;
4. drop table table_name;
table字段篇
1. alter table table_name add 字段;
2. alter table table_name modify 字段; 該字段用的是舊名畏线，設(shè)置新的格式
3. alter table table_name change old_column_name new_column_name 字段格式;
4. alter table table_name drop column column_name;
table數(shù)據(jù)篇
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:標(biāo)示要?jiǎng)h的行數(shù)據(jù)
4. truncate table_name; 刪除表格，重建列表
table查詢篇
1. select * from table_name where id=23;
2. select id from data_name.table_name where id=43;
3. select * from table_name where id=43 and name='haha';#and左右鏈接的兩個(gè)條件同時(shí)滿足
4. select * from table_name where id=43 or name='haha';#or左右兩個(gè)條件都作為要挑選的分段锦庸，即滿足左條件select數(shù)據(jù)漂辐、滿足右條件select數(shù)據(jù)其屏，select出來的兩種數(shù)據(jù)并存不沖突
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;
6. select name,age from table_name order by age asc;
7. select name.age from table_name where age=32 and order by name;
8. select name,age from table_name where age!=19 and age!=23;
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);
11. #select name,age from table_name where height=null;
12. select name,age from table_name where height is null;
13. select name,age from table_name where birthday between '1990/2/2' and '2000/3/2';
14. select name,age from table_name order by age limit 3,4;
15. select name from table_name order by age limit 4 offset 3;
