一、數(shù)據(jù)庫操作：?

?1.1 創(chuàng)建數(shù)據(jù)庫：? ?

?create database student；?

?1.2? 刪除數(shù)據(jù)庫：? ?

?drop database student；

二、數(shù)據(jù)庫表操作：

? 2.1 創(chuàng)建數(shù)據(jù)庫表? ?

?create table 表名；??

? create table class2 (name char(4),sex char(2),age int(3));??

? create table class3(id char(10) primary key,name char(3) not null,sex char(2),age int(4) default '20');?

?primary key 主鍵?

?not null? 不能為空

? Default? 默認值??

?創(chuàng)建表時多列設為主鍵：

? create table test(adress char(20),id int(3),name char(10),primary key(id,name));? ?

?? 創(chuàng)建表時設置ID自增長：

? create table users ( id int(5) not null auto_increment, name varchar(20)not null, primary key (`id`) );

自增長插入數(shù)據(jù)：

mysql> insert into users values('','liming'),('','zhangsan');

Query OK, 2 rows affectedRecords: 2? Duplicates: 0? Warnings: 0??

2.2 查看表結構：?

?desc 表名；

? desc student;??

?2.3 查看有哪些表??

? show tables;??

?2.4 刪除表： ?

?drop table 表名；?

?2.5 修改表：? ?

?2.5.1 rename ??

? ?語法結構：alter table 舊表名? rename 新表名；??

? ? alter table test rename class;? ?

?2.5.2對表結構修改? ?

?? 增加一列 add??

? ? alter table class add 地址 char(20);??

? 2.5.3 刪除一列 drop??

? ? alter table class drop 表結構；

? ? 2.5.4 修改列 change? ?

?? alter table 表名 change 姓名 name char(3);?

?三、表中數(shù)據(jù)的操作：

? 3.1插入數(shù)據(jù)：??

? 3.1.1 插入一條數(shù)據(jù)：?

?insert into 表名（id,name,sex,age） values(001,趙四，男，20)；? ? ? insert into 表名 values（值1，值2...）;-----values中的值必須和表的默認結構對應/? ??

? insert into class values('wuhuishan','女',20,'006');? ?

?3.1.2 一次性寫入多條數(shù)據(jù)??

? ? insert into 表名（屬性1，屬性2..） values(值1.1，值2.1...),(值1.2，值2.2,....);? ? ?

?insert into class(id,name,sex,age) values('001','zhangsan','男',20),('002','lisi','男',21);?

?? 3.2 刪除數(shù)據(jù)：??

? 3.2.1 delete 語句delete from 表名；--一次性刪除所有數(shù)據(jù)了??

? delete from 表名 where語句：??

? delete from class where name='lisi';? ?

?3.2.2 truncate語句??

? truncate 表名；---清空數(shù)據(jù)庫表? ?

?truncate class;

?delete truncate 區(qū)別? 清空數(shù)據(jù)庫表時truncate 效率更高，delete可以跟條件語句而truncate不能??

? 3.3 修改表中數(shù)據(jù)：? ?

?? update? set? ??

? update 表名? set 需要更新的數(shù)據(jù) where：?

?? ? update class set id='002' where id='004';??

? 3.4? 查詢語句：??

? ? 3.4.1 簡單查詢語句? ??

? select * from 表名；---*代表查詢所有列? ?

?? select 需要查詢的屬性 from 表名 （where）；? ? ?

?? ? select address,name,birth from student;---查詢指定的列address,name,birth ??

?? 3.4.2 where語句查詢：

? select * from student where birth=1990 or birth=1991；? ---查看生日是1990和1991的所有學生信息?

?select * from? student where birth！=1990； ?------查看生日不是1990的所有學生信息? ??

? select * from student where not birth=1990;? ------查看生日不是1990的所有學生信息? ??

? select goods_name from ecs_goods where is_delete=0 and is_new=1; ----查詢goods表中沒有被is_delete=0刪除并且為is_new=1新品? ? ??

查詢 goods表中的shop_price價格 大于4000，小于8000的產(chǎn)品信息? ? ? select goods_name，shop_price from ecs_goods where 40004000 and shop_price<8000;? ----正確寫法? ? ?

?查詢沒有刪除的產(chǎn)品中，要么是新品，要么是熱銷，要么精品? ? ? select goods_name from ecs_goods where is_delete=0 and （is_new=1 or is_hot=1 or is_best=1）；? ? ?

?查詢出點擊量click_count超過10次，并且沒有庫存的產(chǎn)品信息? ? ? select click_count,goods_name,goods_number from ecs_goods where click_count>10 and goods_number=0;? ? ?

?查詢出并且熱銷is_hot=1產(chǎn)品庫存小于5? ?

?? select goods_number,goods_name from ecs_goods where is_hot=1 and goods_number<5;? ? ??

?找出刪除的產(chǎn)品哪些是熱銷，精品，新品? ?

?? select goods_name from ecs_goods where is_delete=0 and （is_new=1 and? is_hot=1 and is_best=1）；? ? ??

3.4.3 模糊查詢：like? ? ?

?? % 全匹配??

? ? ? _ 單個字符匹配? ? ?

?? 商品名稱包含18k 的產(chǎn)品有哪些？? ??

? ? select goods_name from ecs_goods where goods_name like '%18K%' ;? ? ?

?? select goods_name from ecs_goods where goods_name like '____18K_____' ;? ??

? ? 查詢產(chǎn)品名稱包含18K的并且產(chǎn)品的庫存為0的產(chǎn)品名稱? ?

?? ? select goods_name from ecs_goods where goods_name like '%18K%' and goods_number=0;? ??

? ? 查詢產(chǎn)品名稱包含18K的并且產(chǎn)品促銷中顯示"國慶大促"? ? ?

?? select goods_name,goods_brief from ecs_goods where goods_name like '%18K%' and goods_brief like '%國慶大促%';?

?3.4.4 in 在..里面? ??

? ? ? 查詢產(chǎn)品編號為ECS000136，ECS000137，ECS000138的產(chǎn)品信息? ? ? ??

? select goods_sn from ecs_goods where goods_sn='ECS000136' or goods_sn='ECS000137' or goods_sn='ECS000138' ；---查詢內(nèi)容為字符串時必須用引號引起來? ?

?? ? ? 使用in時：? ? ??

? ? select goods_sn from ecs_goods where goods_sn in ('ECS000136','ECS000137','ECS000138')；? ?

?? ? ? ? 查詢出有訂單的產(chǎn)品的所有信息(ecs_order_goods 訂單表)? ? ? ? ? 1、產(chǎn)品信息 在產(chǎn)品表中? ? ? ? ??

? select goods_name，goods_sn,goods_id from ecs_goods；? ? ? ? ? 2、產(chǎn)品存在在訂單表中? ? ? ? ?

?? select goods_id from ecs_order_goods;? ? ? ? ?

?? 3、 綜合查詢： ? ? ? ? ??

select goods_name，goods_sn,goods_id from ecs_goods where goods_id in （select goods_id from ecs_order_goods） ；? ?

?? 成績大于80分的學生信息；? ??

? ? ? select * from student where grade in (select grade from student where grade>80);? ? ?

?? ? 3.4.5? between? and ----介于什么和什么之間? ??

? ? ? between 較小的數(shù)值 and 較大的數(shù)值；? ? ?

?? ? select goods_name，shop_price from? ecs_goods where shop_price between 4000 and 8000；? ? ?

?? 3.4.6 算數(shù)運算符使用 + - * /? mod? ??

? ? 查詢出market_price,shop_price之和大于15000；

? ? ? ? select market_price,shop_price,market_price+shop_price,goods_name from ecs_goods where market_price+shop_price>15000;

查詢出售價與超市價差價大于1000的商品信息：

select market_price,shop_price,market_price-shop_price,goods_name from ecs_goods where market_price-shop_price>1000;? ? ? ??

商品預售額大于100000的商品信息；?

?? ? ? select goods_number,shop_price,goods_number*shop_price from ecs_goods where goods_number*shop_price>100000;? ? ?

?? 查詢出產(chǎn)品的折扣大于8折? ??

? ? select shop_price/market_price from ecs_goods where shop_price/market_price<0.8;

mod 求余? ? ?

?? select goods_number,goods_number mod 3 from ecs_goods where? (goods_number mod 3)=0;

select goods_number,goods_number mod 3 from ecs_goods where (goods_number mod 3) =0;

?select goods_number,goods_number mod 3 from ecs_goods;

3.4.7? 排序? order by? 排序 升序排序? ??

? select shop_price from ecs_goods order by shop_price;? ? ?

?? order by decs? 降序排序??

? ? select shop_price from ecs_goods order by shop_price desc;? ? 3.4.8 查詢空值? ?

?? is null? ? ? 查詢出 is_check為null的數(shù)據(jù)? ??

? select * from ecs_goods where is_check is null;? ?

?? 3.4.9 函數(shù)? ? ?

?count 統(tǒng)計函數(shù)? ? ? 統(tǒng)計ecs_goods總數(shù) ??

? select count(goods_number) from ecs_goods;? ? ? ??

?sum 求和函數(shù)? ? ?

?select sum(goods_number) from ecs_goods;? ??

? avg 求平均值? ?

?select avg(shop_price) from ecs_goods;? ??

? ? max? 最大值? ?

?select max(shop_price) from ecs_goods;? ??

? min 最小? ?

?? select min(shop_price) from ecs_goods;? ? ? ?

?? distinct? 去重復? ??

? select distinct(goods_number) from ecs_goods;?

?? 3.4.10? group by? 分組? ? ??

select goods_number,count(goods_number) from ecs_goods group by goods_number;? ??

? ? 3.4.11 limit? 用法? 查詢行數(shù)? ? ?

?查詢2-4行? ?

?? limit 1,3? ---第一個數(shù)指的是當前所在的行，第二數(shù)是讓你顯示的行數(shù)? ? ? limit 1,3 從當前第一行開始的下一行顯示3行數(shù)據(jù)? ? ? 如果要求顯示前三行 limit0,3? ??

? 顯示價格最高的前三個? ??

? select shop_price from ecs_goods order by? shop_price desc limit0,3;? ?

?? 3.4.12 having 條件語句? ? ?

?? select shop_price from ecs_goods where shop_price>10000;? ? ? ? select shop_price from ecs_goods having shop_price>10000;? ? ? ? ? ? having? 進行分組以后只能用having? ?

?? ? 對產(chǎn)品的數(shù)量進行分組，顯示同一數(shù)量的商品統(tǒng)計后大于兩個的? ? ? ? select goods_number,count(goods_number) from ecs_goods group by goods_number? having count(goods_number)>2;? ? ?

?求出未刪除的產(chǎn)品熱銷產(chǎn)品與非熱銷產(chǎn)品的數(shù)量大于2的? ?

?? ? select is_hot,count(*) from ecs_goods where is_delete=0 group by is_hot? having count(*)>2? ;? ??

? 3.4.13? 子查詢? in? ? ? ? any? ? ? ? ? some? ? ? ?

?? ? 要求查詢student表中比任意一個class表中年齡大的人信息；? ? ? ? ? select 2017-birth from student where (2017-birth)(select avg(2017-birth) from student);

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)

SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)；

3.4.14 合并 union? 合并

1、兩個表列要一致

2、存在重復時去重；

select * from student union select * from student2;

select id from student union select id from class;

select id,name from test1 union select id,name from test2;

3.4.15 左右連接：

select ..from? 表1 連接類型 join 表2? on 連接關系；

select * from student left join sc on student.id=sc.stu_id;

select * from test1 right join test2 on test1.id=test2.id;

3.4.16 多表連接：

找到表與表的對應關系

select 查詢的列屬性 from 表1 a1，表2 a2.. where 條件 表1.屬性=表2.屬性 and 表3.屬性=表2.屬性；

1、如果多張表中有同一個屬性名時必須標注是哪個表中的屬性，student.id ,s1.id;

2、表的別名表示法

mysql> select * from student;

+-----+--------+-----+-------+------------+--------------+

| id? | name? | sex | birth | department | address? ? ? |

+-----+--------+-----+-------+------------+--------------+

| 901 | 張老大 | 男? |? 1985 | 計算機系? | 北京市海淀區(qū) |

| 902 | 張老二 | 男? |? 1986 | 中文系? ? | 北京市昌平區(qū) |

| 903 | 張三? | 女? |? 1990 | 中文系? ? | 湖南省永州市 |

| 904 | 李四? | 男? |? 1990 | 英語系? ? | 遼寧省阜新市 |

| 905 | 王五? | 女? |? 1991 | 英語系? ? | 福建省廈門市 |

| 906 | 王六? | 男? |? 1988 | 計算機系? | 湖南省衡陽市 |

+-----+--------+-----+-------+------------+--------------+

mysql> select * from score;

+----+--------+--------+-------+

| id | stu_id | c_name | grade |

+----+--------+--------+-------+

|? 1 |? ? 901 | 計算機 |? ? 98 |

|? 2 |? ? 901 | 英語? |? ? 80 |

|? 3 |? ? 902 | 計算機 |? ? 65 |

|? 4 |? ? 902 | 中文? |? ? 88 |

|? 5 |? ? 903 | 中文? |? ? 95 |

|? 6 |? ? 904 | 計算機 |? ? 70 |

|? 7 |? ? 904 | 英語? |? ? 92 |

|? 8 |? ? 905 | 英語? |? ? 94 |

|? 9 |? ? 906 | 計算機 |? ? 90 |

| 10 |? ? 906 | 英語? |? ? 85 |

+----+--------+--------+-------+

1、查詢同時參加計算機和英語考試的學生的信息

a、select stu_id from score where stu_id in(select stu_id from score where c_name='計算機' ) and c_name='英語';

mysql>? select id,name,sex,birth,department,address from student where id in (select stu_id from score where stu_id in(select stu_id from score where c_name='計算機' ) and c_name='英語');

+-----+--------+-----+-------+------------+--------------+

| id? | name? | sex | birth | department | address? ? ? |

+-----+--------+-----+-------+------------+--------------+

| 901 | 張老大 | 男? |? 1985 | 計算機系? | 北京市海淀區(qū) |

| 904 | 李四? | 男? |? 1990 | 英語系? ? | 遼寧省阜新市 |

| 906 | 王六? | 男? |? 1988 | 計算機系? | 湖南省衡陽市 |

+-----+--------+-----+-------+------------+--------------+

mysql> select s1.id,name,sex,birth,department,address from student s1,score s2,score s3 where s1.id=s2.stu_id and s2.stu_id=s3.stu_id and s2.c_name='計算機' and s3.c_name='英語' ;

+-----+--------+-----+-------+------------+--------------+

| id? | name? | sex | birth | department | address? ? ? |

+-----+--------+-----+-------+------------+--------------+

| 901 | 張老大 | 男? |? 1985 | 計算機系? | 北京市海淀區(qū) |

| 904 | 李四? | 男? |? 1990 | 英語系? ? | 遼寧省阜新市 |

| 906 | 王六? | 男? |? 1988 | 計算機系? | 湖南省衡陽市 |

+-----+--------+-----+-------+------------+--------------+

3 rows in set

mysql> select s1.id,name,sex,birth,department,address from student s1,score s2,score s3 where s1.id=s2.stu_id and s2.stu_id=s3.stu_id and s2.c_name='計算機' and s2.c_name='英語' ;--------表s2和表s3中同時存在計算機和英語時不存在；

Empty set

4、索引：

1、分類

2、增加索引方法

3、索引優(yōu)缺點

mysql>? alter table student add index cxy(name);

Query OK, 6 rows affected

Records: 6? Duplicates: 0? Warnings: 0

mysql> show index from student;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table? | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| student |? ? ? ? ? 0 | PRIMARY? |? ? ? ? ? ? 1 | id? ? ? ? ? | A? ? ? ? |? ? ? ? ? 6 | NULL? ? | NULL? |? ? ? | BTREE? ? ? |? ? ? ? |

| student |? ? ? ? ? 1 | cxy? ? ? |? ? ? ? ? ? 1 | name? ? ? ? | A? ? ? ? | NULL? ? ? ? | NULL? ? | NULL? |? ? ? | BTREE? ? ? |? ? ? ? |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set

復制表：as 復制表并且復制內(nèi)容? like只復制表結構

mysql> create table test3 like test2;

Query OK, 0 rows affected

mysql> show tables;

+-------------------+

| Tables_in_cxy23-2 |

+-------------------+

| class? ? ? ? ? ? |

| score? ? ? ? ? ? |

| student? ? ? ? ? |

| student2? ? ? ? ? |

| test1? ? ? ? ? ? |

| test2? ? ? ? ? ? |

| test3? ? ? ? ? ? |

+-------------------+

7 rows in set

mysql> select * from test3;

Empty set

mysql> desc test3;

+-------+-----------+------+-----+---------+-------+

| Field | Type? ? ? | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------+-------+

| id? ? | int(11)? | NO? | PRI | NULL? ? |? ? ? |

| name? | char(255) | YES? |? ? | NULL? ? |? ? ? |

+-------+-----------+------+-----+---------+-------+

2 rows in set

mysql> create table test4 as select * from test2;

Query OK, 4 rows affected

Records: 4? Duplicates: 0? Warnings: 0

mysql> show tables;

+-------------------+

| Tables_in_cxy23-2 |

+-------------------+

| class? ? ? ? ? ? |

| score? ? ? ? ? ? |

| student? ? ? ? ? |

| student2? ? ? ? ? |

| test1? ? ? ? ? ? |

| test2? ? ? ? ? ? |

| test3? ? ? ? ? ? |

| test4? ? ? ? ? ? |

+-------------------+

8 rows in set

mysql> select * from test4;

+----+-------+

| id | name? |

+----+-------+

|? 1 | user1 |

|? 2 | user2 |

|? 6 | user6 |

|? 7 | user7 |

+----+-------+

4 rows in set

mysql>

mysql> select timeday,result,count(*) from aaaa group by timeday,result;

+------------+--------+----------+

| timeday? ? | result | count(*) |

+------------+--------+----------+

| 2005-05-09 | 勝? ? |? ? ? ? 2 |

| 2005-05-09 | 負? ? |? ? ? ? 2 |

| 2005-05-10 | 勝? ? |? ? ? ? 1 |

| 2005-05-10 | 負? ? |? ? ? ? 2 |

+------------+--------+----------+

4 rows in set

mysql> select * from aaaa;

+------------+--------+

| timeday? ? | result |

+------------+--------+

| 2005-05-09 | 勝? ? |

| 2005-05-09 | 負? ? |

| 2005-05-09 | 負? ? |

| 2005-05-09 | 勝? ? |

| 2005-05-10 | 勝? ? |

| 2005-05-10 | 負? ? |

| 2005-05-10 | 負? ? |

+------------+--------+

7 rows in set