mysql常用sql與命令之從入門到刪庫跑路

create table 表名(
	列名1 數(shù)據(jù)類型1,
	列名2 數(shù)據(jù)類型2,
	....
	列名n 數(shù)據(jù)類型n
	);

insert into user2 values (1,'李四','123'); // 增
delete from pet where ower = 'disn'; //刪
update pet set name = '后裔' where ower = 'dfn'; //改

①> 、 、= 、>= 、= 、>	
②BETWEEN...AND	
③ IN( 集合)	
④LIKE 模糊查詢	
⑤_單個任意字符
⑥%多個任意字符
⑦IS NULL 
⑧and 或 
⑨or 或 || 
⑩not 或 !
查詢條件應(yīng)用舉例:
SELECT * FROM user WHERE age >= 18;
SELECT * FROM user WHERE age >= 18 AND age =36;
SELECT * FROM user WHERE age BETWEEN 40 AND 70;
SELECT * FROM user WHERE age IN (6,18,37);
// 關(guān)于NULL
SELECT * FROM user WHERE height = NULL; 錯誤,因為null值不能使用=或（!=) 判斷
SELECT * FROM user WHERE height IS NULL;(正確)
SELECT * FROM user WHERE height IS NOT NULL;(正確)
// 查詢姓陳的有哪些？ like>
SELECT * FROM user WHERE NAME LIKE '陳%';
// 查詢姓名第二個字是新的人
SELECT * FROM user WHERE NAME LIKE "_新%";
// 查詢姓名是三個字的人
SELECT * FROM user WHERE NAME LIKE '___';
// 查詢姓名中包含狗的人
SELECT * FROM user WHERE NAME LIKE '%狗%';

 create table user (
	id int primary key auto_increment, // 在創(chuàng)建表時，添加主鍵約束，并且完成主鍵自增	
	name varchar(20)
 );
-- 聯(lián)合主鍵: 由多個字段聯(lián)合組成的主鍵, 只要聯(lián)合的主鍵加起來不重復(fù)就可以.聯(lián)合主鍵中的任何一個字段都不能為空.
create table user2 (
 	id int,
 	name varchar(20),
 	password varchar(20),
 	primary key(id, name)
);

 create table user1 (
 	id int primary key auto_increment,
 	phone_num varchar(20) unique
 	 );
 create table user2 (
 	id int primary key auto_increment,
 	name varchar(20),
 	unique(id, name) // 表示兩個字段在一起不重復(fù)就可以
 	 );
 	 

create table user3 (
	id int primary key auto_increment,
	name varchar(20) not null
	);

create table user4(
	id int primary key auto_increment,
	age int default 18,
	name varchar(20) not null
	);

create table 表名(
....
外鍵列
constraint 外鍵名稱 foreign key (外鍵列名稱) references 主表名稱(主表列名稱)
);
// 班級
create table classes(
	id int primary key,
	name varchar(20)
	);	
// 學(xué)生表
create table student (
		id	int primary key,
		name varchar(20),
		class_id int,
		foreign key(class_id) references classes(id)
		);
		

查詢所有記錄

例如:查詢student表中的所有記錄.

select * from student;

查詢指定字段

例如:查詢student中的sname,ssex,class.

select sname,ssex,class from student;

查詢教師表中所有的單位即不重復(fù)的depart列. 排除重復(fù)distinct>

select distinct depart from teacher;

查詢score表中成績在60到80之間的所有記錄 查詢區(qū)間 between…and…>

select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree  80;

查詢score表中成績?yōu)?5,86或88的記錄

select * from score where degree in(85, 86, 88);

查詢student表中'95031'班或性別為'女'的同學(xué)記錄. or 表示或者>

select *from student where class = '95031' or sex = '女';

以class降序查詢student表的所有記錄 降序:desc, 升序asc,默認(rèn)升序(省略)>.

select * from student order by class desc;

以cno升序,degree降序查詢score表的所有記錄

select * from score order by cno asc,degree desc;

查詢"95031'班的學(xué)生人數(shù) 統(tǒng)計 count>

select count(*) from student where class = '95031';
查詢score表中最高分的學(xué)生學(xué)號和課程號(子查詢)

select sno, cno from score where degree = (select max(degree) from score );其中:select max(degree) from score 先查出最高分.

select sno,cno degree from score order by degree desc limit 0,1;其中:limit第一個數(shù)字表示從多少開始,第二個表示多少條.當(dāng)有多個相同最高分時,容易出bug,不推薦使用這種方式查詢.

查詢每門課的平均成績

select cno, avg(degree) from score group by cno;

查詢score表中至少有2名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù).

select cno, avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';

查詢分?jǐn)?shù)大于70, 小于90的sno列.

select sno, degree from score where degree between 70 and 90;
查詢所有學(xué)生的sname, cno和degree列.
select sname, cno, degree from student, score where student.sno = score.sno;
查詢所有學(xué)生的sno,cname和degree列
select sno,cname,degree from course ,score where course.cno = score.cno;
查詢"95031"班學(xué)生每門課的平均分.
select cno, avg(degree) from score where sno in (select sno from student where class = '95031') group by cno;
查詢選修"3-105"課程的成績高于"109"號同學(xué)"3-105"成績的所有同學(xué)的記錄.
select * from score where cno = '3-105' and degree > (select degree from score where sno = '109' and cno = '3-105');
查詢成績高于學(xué)號為"109", 課程號為"3-105"的成績的所有記錄
select * from score where degree > (select degree from score where sno = '109' and cno = '3-105');
查詢和學(xué)號為108,101的同學(xué)同年出生的所有的sno, sname, sbirthday
select *from student where year(sbirthday) in (select year(sbirthday) from student where sno in(108, 101));
查詢"張旭"教師任課的學(xué)生成績
select * from score where cno = ( select cno from course where tno = (select tno from teacher where tname = "張旭"));
查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名.
select tname from teacher where tno = (select tno from course where cno = (select cno from score group by cno having count(*) > 5));
查詢存在有85分以上的成績的課程的cno
select cno, degree from score where degree > 85;
查詢出"計算機(jī)系"教師所教課程的成績表
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart = "計算機(jī)系"));
查詢選修編號為"3-105"課程且成績至少高于選休息編號為"3-245"的同學(xué)的cno,sno和degree,并按degree從高到低次序排序.
any 至少一個.

select * from score where cno = '3-105' and degree > any(select degree from score where cno = '3-245') order by degree desc;

select * from score where cno = '3-105' and degree > all(select degree from score where cno = '3-245') order by degree desc;

select * from score a where degree  (select avg(degree) from score b where a.cno = b.cno);
表a
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 91 |
| 102 | 3-105 | 92 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 81 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec) 

表b
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 91 |
| 102 | 3-105 | 92 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 81 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec) 

分析用例的數(shù)據(jù)準(zhǔn)備:
mysql> select * from person;
+----+--------+--------+
| id | name | cardId |
+----+--------+--------+
| 1 | 張三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+----+--------+--------+
3 rows in set (0.00 sec)
mysql> select * from card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 飯卡 |
| 2 | 建行卡 |
| 3 | 農(nóng)行卡 |
| 4 | 工商卡 |
| 5 | 郵政卡 |
+------+-----------+
5 rows in set (0.00 sec)

select * from person inner join card on person.cardId = card.id;
+----+--------+--------+------+-----------+
| id | name | cardId | id | name |
+----+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
+----+--------+--------+------+-----------+
2 rows in set (0.00 sec)

select * from person left join card on person.cardId = card.id;

+----+--------+--------+------+-----------+
| id | name | cardId | id | name |
+----+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+----+--------+--------+------+-----------+
3 rows in set (0.00 sec)

select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 郵政卡 |
+------+--------+--------+------+-----------+
5 rows in set (0.01 sec)

mysql> select * from person full join card on person.cardId= card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
**** 解決mysql不支持full join的方法****
 左連接 + 右鏈接> , 即通過union來連接左右連接. 左連接 union 右鏈接>.
eg:

select * from person left join card on person.cardId = card.id union select * from person right join card on person.cardId = card.id;

+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 郵政卡 |
+------+--------+--------+------+-----------+
6 rows in set (0.01 sec)

MYSQL執(zhí)行語句順序，嚴(yán)格遵循次順序，不能改變
select
from
where
group by
having
order by

例如:
a --> -100
update user set money = money - 100 where name = 'a';
b --> +100
update user set money = money + 100 where name = 'b';
-- 實際程序中, 如果只有一條sql語句執(zhí)行成功了,而另外一條沒有執(zhí)行成功?則會出現(xiàn)前后數(shù)據(jù)不一致的情況.
update user set money = money - 100 where name = 'a';
update user set money = money + 100 where name = 'b';
在多條sql語句,可能會有同時成功的要求,要么就同時失敗.

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|  1 |
+--------------+
1 row in set (0.00 sec)
// 建表
create database bank;
create table user (
	id int primary key,
 name varchar(20),
 money int
 );
// 首先在表中插入一條用戶數(shù)據(jù)a.
insert into user values (1,'a',1000);
Query OK, 1 row affected (0.00 sec)
// 進(jìn)行回滾操作.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// 執(zhí)行回滾后,查看數(shù)據(jù)表信息,發(fā)現(xiàn)即使調(diào)用了rollback,但插入的數(shù)據(jù)依然存在.說明當(dāng)前不能回滾.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
// 可以通過設(shè)置msql的回滾自動提交為false.
set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|  0 |
+--------------+
1 row in set (0.00 sec)
// 也就說, 通過上面的set autocommit = 0;操作關(guān)閉了mysql的自動提交(commit).
*******再次插入數(shù)據(jù):*******
insert into user values (2,'b',1000);
Query OK, 1 row affected (0.00 sec)
// 插入數(shù)據(jù)后查看表,用戶2數(shù)據(jù)添加成功.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
// 執(zhí)行回滾操作.
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
// 回滾后再次查看表,發(fā)現(xiàn)剛才插入的數(shù)據(jù)已經(jīng)被干掉了.
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.01 sec)
**** 對于這種場景,如果想讓用戶b數(shù)據(jù)成功提交, 可以通過commit;命令執(zhí)行手動提交操作.手動提交后,如果想再次通過rollback來撤銷,則是不可以的.也就是說,事務(wù)一旦提交,執(zhí)行的sql語句就不可以再撤銷,也就是說事務(wù)一旦提交數(shù)據(jù)就會持久的產(chǎn)生效果.

即:
 當(dāng)前默認(rèn)為自動提交模式,此時執(zhí)行rollback無效.執(zhí)行下面sql語句:
 start transaction;(或者begin;)
 update user set money = money - 100 where name = 'a';
 update user set money = money + 100 where name = 'b';
 執(zhí)行完插入a,b用戶數(shù)據(jù)后,再執(zhí)行rollback,發(fā)現(xiàn)可以成功回滾事務(wù).可以成功切換成手動開啟事務(wù)的模式.若想使得插入的數(shù)據(jù)生效,也需要手動執(zhí)行commit進(jìn)行提交操作.
 事務(wù)開啟之后,一旦commit提交,就不可以回滾,也就說,當(dāng)前的這個事務(wù)在提交的時候就已經(jīng)結(jié)束了.
 

mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ  |
+--------------------------------+
1 row in set (0.00 sec)

例如:
create table myorder(
		product_id int,
		customer_id int,
		product_name varchar(20),
		customer_name varchar(20),
		primary key(product_id, customer_id
	);
	當(dāng)前表中, 除主鍵以外的其他列, 只依賴于主鍵的部分字段.則不滿足第二范式,通常需要拆表.
create table myorder(
		order_id int primary key,
		product_id int,
		customer_id int
	);
create table product (
		id int primary key,
		name varchar(20)
	);
create table customer(
		id int primary key,
		name varchar(20)
		);
拆分成三個表后,滿足第二范式.

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(20) not null,
	sbrithday datetime,
	class varchar(20)
	);

create table student(
	sno varchar(20) primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
)

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
	);

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
	);

create table score(
	sno varchar(20) not null,
	degree decimal,
	primary key (sno, cno),
	foreign key (sno) references student(sno),
	foreign key (cno) references course(cno)
	);

insert into student values ('101','曾華','男','1977-09-01','95033');
insert into student values ('102','匡明','男','1975-10-02','95031');
insert into student values ('103','王麗','女','1976-01-23','95033');
insert into student values ('104','李軍','男','1976-02-20','95033');
insert into student values ('105','王芳','女','1975-02-10','95031');
insert into student values ('106','陸君','男','1974-06-03','95031');
insert into student values ('107','王尼瑪','男','1976-02-20','95033');
insert into student values ('108','張全蛋','男','1975-02-10','95031');
insert into student values ('109','趙鐵柱','男','1974-06-03','95031');

insert into teacher values ('804','李成','男','1958-12-02','副教授','計算機(jī)系');
insert into teacher values ('856','張旭','男','1969-03-12','講師','電子工程系');
insert into teacher values ('825','王萍','女','1972-05-05','助教','計算機(jī)系');
insert into teacher values ('831','劉冰','女','1977-08-14','助教','電子工程系');

insert into course values ('3-105','計算機(jī)導(dǎo)論', '825');
insert into course values ('3-245','操作系統(tǒng)', '804');
insert into course values ('6-166','數(shù)字電路', '856');
insert into course values ('9-888','高等數(shù)學(xué)', '831');
 
insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');


create table person(
	id int primary key auto_increment,
	name varchar(20),
	cardId int
);

create table card (
	id int,
	name varchar(20)
);

insert into card values (1,'飯卡');
insert into card values (2,'建行卡');
insert into card values (3,'農(nóng)行卡');
insert into card values (4,'工商卡');
insert into card values (5,'郵政卡');

insert into person values (1,'張三',1);
insert into person values (2,'李四',3);
insert into person values (3,'王五',6);