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

mysql總結(jié):database篇

#:代表錯(cuò)誤對比

1. create database data_name charset utf8; 創(chuàng)建database并設(shè)置字符集
2. create database data_name charact set=utf8;　
   create database if not exists data_name charset utf8;
3. show create database data_name; 查看database參數(shù)
4. #show database status; 查看整個(gè)數(shù)據(jù)庫的參數(shù)狀態(tài);
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總結(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 '表明'; 創(chuàng)建table
    show create table table_name; 查看table參數(shù)
    show table status \G; 查看全部tables參數(shù)
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 字段; 該字段用的是舊名，設(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'); 添加數(shù)據(jù)
2. updata table_name set column_name='haha' where id=23;　修改數(shù)據(jù)
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; 依據(jù)id選擇全部信息
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; 按年齡降序選擇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數(shù)據(jù)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的數(shù)據(jù)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'范圍的數(shù)據(jù)name,age
14. select name,age from table_name order by age limit 3,4; 按年齡排序索引為3包括本行后4行　注:數(shù)據(jù)索引從0開始
15. select name from table_name order by age limit 4 offset 3; offset偏移量為４，從索引為３開始