MySQL之SQL基礎(chǔ)

SQL是Structure Query Language(結(jié)構(gòu)化查詢語(yǔ)言）的縮寫，它是關(guān)系型數(shù)據(jù)庫(kù)的應(yīng)用語(yǔ)言，由IBM在20世紀(jì)70年×××發(fā)，以實(shí)現(xiàn)關(guān)系型數(shù)據(jù)庫(kù)中的信息檢索。

成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括忻府網(wǎng)站建設(shè)、忻府網(wǎng)站制作、忻府網(wǎng)頁(yè)制作以及忻府網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，忻府網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到忻府省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

在20世紀(jì)80年代初，美國(guó)國(guó)家標(biāo)準(zhǔn)局（ANSI)開(kāi)始著手制定SQL標(biāo)準(zhǔn)，最早的ANSI標(biāo)準(zhǔn)于1986年完成，就被叫做SQL-86。正是由于SQL語(yǔ)言的標(biāo)準(zhǔn)化，所以大多數(shù)關(guān)系型數(shù)據(jù)庫(kù)都支持SQL語(yǔ)言，它已經(jīng)發(fā)展成為多種平臺(tái)進(jìn)行交互操作的底層會(huì)話語(yǔ)言。

SQL的分類：

DDL：數(shù)據(jù)定義語(yǔ)言，即是對(duì)數(shù)據(jù)庫(kù)內(nèi)部對(duì)象進(jìn)行創(chuàng)建、刪除、修改等操作的語(yǔ)言，和DML最大區(qū)別在于DML僅對(duì)表內(nèi)數(shù)據(jù)進(jìn)行操作，而不涉及到表的定義、結(jié)構(gòu)的修改，更不會(huì)涉及其它對(duì)象，DBA使用較多。常用關(guān)鍵字包括create、drop、alter等。
DML：數(shù)據(jù)操作語(yǔ)言，用于添加、刪除、更新和查詢表中的記錄，并檢查數(shù)據(jù)的完整性，開(kāi)發(fā)人員使用較多。常用的語(yǔ)句包括insert、delete、update、和select等。
DCL：數(shù)據(jù)控制語(yǔ)言，用于管理系統(tǒng)中的對(duì)象權(quán)限時(shí)使用，常用語(yǔ)句有g(shù)rant、revoke等。

1 DDL語(yǔ)句示例：

1）創(chuàng)建數(shù)據(jù)庫(kù)

MySQL> show engines; #查看支持的引擎，包括默認(rèn)的引擎

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

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

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

mysql> show character set; #查看系統(tǒng)支持的字符集

mysql> show variables like "character%"; #查看當(dāng)前字符集設(shè)置

mysql> show variables like "collation%"; #查看字符集校驗(yàn)設(shè)置

mysql> create database test1; #創(chuàng)建test1數(shù)據(jù)庫(kù)

Query OK, 1 row affected (0.05 sec)

mysql> show databases; #查看數(shù)據(jù)庫(kù)

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test1 |

| testdb |

mysql> create database t121 default character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec) #創(chuàng)建時(shí)，也可以指定字符集

2）刪除數(shù)據(jù)庫(kù)

mysql> drop database test1;

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| testdb |

備注：數(shù)據(jù)庫(kù)一旦被刪除，庫(kù)中所有的表也將被刪除，因此，備份非常重要

3）創(chuàng)建表

mysql> use zwj; #選擇數(shù)據(jù)庫(kù)

mysql> create table emp #varchar(n)其中n代表字符數(shù)

-> (ename varchar(10),

-> hiredate date,

-> sal decimal(10,2),

-> deptno int(2));

Query OK, 0 rows affected (0.11 sec)

mysql> desc zwj.emp; #查看zwj庫(kù)中的emp表的結(jié)構(gòu)

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

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

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

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

mysql> show create table zwj.emp\g #查看創(chuàng)建表的SQL語(yǔ)句，包括使用的字符集

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

| Table | Create Table |

| emp | CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

復(fù)制一張表

mysql> create table t119 like zwj.t118;

Query OK, 0 rows affected (0.04 sec)

mysql> insert into t119 select * from zwj.t118;

Query OK, 12 rows affected (0.00 sec)

Records: 12 Duplicates: 0 Warnings: 0

查看表的狀態(tài)，了解兩張表是否一致

mysql> use zwj;

Database changed

mysql> show table status\G

*************************** 1. row ***************************

Name: t118

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 08:20:18

Update_time: 2017-04-29 08:29:50

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

*************************** 2. row ***************************

Name: t119

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 09:09:12

Update_time: 2017-04-29 09:09:38

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

2 rows in set (0.00 sec)

查看指定表的狀態(tài)信息

mysql> show table status like 't118'\G

*************************** 1. row ***************************

Name: t118

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 08:20:18

Update_time: 2017-04-29 08:29:50

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

4）刪除表

mysql> drop table zwj.emp;

Query OK, 0 rows affected (0.05 sec)

5）修改表，需要用到alter table語(yǔ)句

修改表ename字段的定義，把varchar(10)改為varchar(20)

mysql> alter table emp modify ename varchar(20); #關(guān)鍵字modify用于修改表中字段的定義

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp;

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

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

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

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

在表emp中新增字段age，類型為int(3):

mysql> alter table emp add age int(3); #默認(rèn)排在最后

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp;

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

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

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

| age | int(3) | YES | | NULL | |

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

5 rows in set (0.00 sec)

刪除一個(gè)字段

mysql> alter table emp drop age;

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改字段名稱

mysql> alter table emp change age age1 int(4); #關(guān)鍵字change可以修改表的定義，如字段名

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp;

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

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

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

| age1 | int(4) | YES | | NULL | |

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

修改字段排列順序

mysql> alter table emp add birth date after ename; #新增字段birth，排在ename之后

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp;

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

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

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

| age1 | int(4) | YES | | NULL | |

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

6 rows in set (0.00 sec)

把字段deptno放在age1后面

mysql> alter table emp1 modify deptno int(2) after age1;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp1;

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

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

| age1 | int(4) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

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

4 rows in set (0.00 sec)

把字段age1放在最前面

mysql> alter table emp modify age1 int(4) first;

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc emp;

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

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

| age1 | int(4) | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

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

修改表名：

mysql> alter table emp rename emp1;

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

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

| Tables_in_zwj |

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

| emp1 |

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

1 row in set (0.00 sec)

2 DML語(yǔ)句示例

1）插入記錄

mysql> insert into emp1(age1,ename,birth,deptno) values('555','aaa','2016-10-30','5');

Query OK, 1 row affected (0.03 sec)

也可以不指定字段名稱，但values后面的順序應(yīng)該和字段的排列順序一致

mysql> insert into emp1 values('666','bbb','2016-12-30','8');

mysql> select * from emp1;

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

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

| 555 | aaa | 2016-10-30 | 5 |

| 666 | bbb | 2016-12-30 | 8 |

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

一次插入多條記錄

mysql> insert into emp1(age1,ename,birth,deptno)

-> values ('111','ccc','2011-11-30','4'),

-> ('666','ddd','2014-12-22','11'),

-> ('888','eee','2015-11-30','22'),

-> ('333','fff','2011-04-30','8');

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from emp1;

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

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

| 555 | aaa | 2016-10-30 | 5 |

| 666 | bbb | 2016-12-30 | 8 |

| 111 | ccc | 2011-11-30 | 4 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

| 333 | fff | 2011-04-30 | 8 |

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

6 rows in set (0.00 sec)

2）更新記錄，通過(guò)update命令進(jìn)行更改

mysql> update emp1 set age1=1000 where ename='aaa';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp1;

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

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

| 1000 | aaa | 2016-10-30 | 5 |

| 666 | bbb | 2016-12-30 | 8 |

| 111 | ccc | 2011-11-30 | 4 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

| 333 | fff | 2011-04-30 | 8 |

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

6 rows in set (0.00 sec)

3）刪除記錄：

mysql> delete from emp1 where ename='bbb';

Query OK, 1 row affected (0.02 sec)

mysql> select * from emp1;

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

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

| 111 | 4 | ccc | 2011-11-30 |

| 666 | 11 | ddd | 2014-12-22 |

| 888 | 22 | eee | 2015-11-30 |

| 333 | 8 | fff | 2011-04-30 |

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

4 rows in set (0.00 sec)

4）查詢記錄

mysql> select age1,ename from zwj.emp1;

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

| age1 | ename |

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

| 666 | bbb |

| 111 | ccc |

| 666 | ddd |

| 888 | eee |

| 333 | fff |

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

把表中的記錄去掉重復(fù)后顯示出來(lái)，

mysql> select distinct age1 from emp1; #distinct是關(guān)鍵字，age1是字段名

條件查詢

mysql> select * from emp1 where age1='666';

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

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

| 666 | bbb | 2016-12-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

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

組合條件查詢：

mysql> select * from emp1 where ename='bbb' and birth<'2017-01-01';

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

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

| 666 | bbb | 2016-12-30 | 8 |

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

mysql> select * from emp1 where ename='bbb' or birth<'2017-01-30';

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

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

| 666 | bbb | 2016-12-30 | 8 |

| 111 | ccc | 2011-11-30 | 4 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

| 333 | fff | 2011-04-30 | 8 |

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

5 rows in set (0.00 sec)

模式匹配：

mysql> select * from zwj.emp1 where ename regexp '^c'; #關(guān)鍵字regexp支持正則表達(dá)式

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

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

| 111 | 4 | ccc | 2011-11-30 |

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

1 row in set (0.00 sec)

mysql> select * from zwj.emp1 where ename like 'c_c'; #短橫表示匹配任意單個(gè)字符

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

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

| 111 | 4 | ccc | 2011-11-30 |

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

1 row in set (0.01 sec)

mysql> select * from zwj.emp1 where ename like 'c%'; #%表示任意字符

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

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

| 111 | 4 | ccc | 2011-11-30 |

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

1 row in set (0.00 sec)

排序和限制：關(guān)鍵字order by（默認(rèn)升序排序）

mysql> select * from emp1 order by age1;

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

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

| 111 | ccc | 2011-11-30 | 4 |

| 333 | fff | 2011-04-30 | 8 |

| 666 | bbb | 2016-12-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

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

5 rows in set (0.00 sec)

對(duì)age1相同的記錄，如果把字段deptno從高到低排列，可使用如下命令，desc表示降序。

mysql> select * from emp1 order by age1,deptno desc;

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

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

| 111 | ccc | 2011-11-30 | 4 |

| 333 | fff | 2011-04-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

| 666 | bbb | 2016-12-30 | 8 |

| 888 | eee | 2015-11-30 | 22 |

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

5 rows in set (0.01 sec)

對(duì)age1相同的記錄，如果把字段deptno從低到高排列，可使用如下命令，asc表示升序。

mysql> select * from emp1 order by age1,deptno asc;

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

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

| 111 | ccc | 2011-11-30 | 4 |

| 333 | fff | 2011-04-30 | 8 |

| 666 | bbb | 2016-12-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

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

5 rows in set (0.01 sec)

選擇排序后的前3條記錄

mysql> select * from emp1 order by age1 limit 3;

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

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

| 111 | ccc | 2011-11-30 | 4 |

| 333 | fff | 2011-04-30 | 8 |

| 666 | bbb | 2016-12-30 | 8 |

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

3 rows in set (0.00 sec)

降序排列后的前3條記錄

mysql> select * from emp1 order by age1 desc limit 3;

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

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

| 888 | eee | 2015-11-30 | 22 |

| 666 | bbb | 2016-12-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

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

3 rows in set (0.00 sec)

顯示以age1字段排序后，從第2條記錄開(kāi)始的前4條記錄

mysql> select * from emp1 order by age1 limit 1,4;

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

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

| 333 | fff | 2011-04-30 | 8 |

| 666 | bbb | 2016-12-30 | 8 |

| 666 | ddd | 2014-12-22 | 11 |

| 888 | eee | 2015-11-30 | 22 |

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

4 rows in set (0.00 sec)

3 DCL語(yǔ)句示例：

新建用戶并且授權(quán)

mysql> grant select,insert on mysql.* to 'abc'@'localhost' identified by 'abc';

Query OK, 0 rows affected (0.08 sec)

撤消權(quán)限

mysql> revoke insert on mysql.* from 'abc'@'localhost';

Query OK, 0 rows affected (0.00 sec)

查看當(dāng)前用戶權(quán)限

mysql> show grants;

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

| Grants for root@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |

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

查看某個(gè)用戶權(quán)限

mysql> show grants for 'abc'@'localhost';

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

| Grants for abc@localhost |

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

| GRANT USAGE ON *.* TO 'abc'@'localhost' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' |

| GRANT SELECT ON `mysql`.* TO 'abc'@'localhost' |

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

2 rows in set (0.00 sec)

本文名稱：MySQL之SQL基礎(chǔ)
文章分享：http://chinadenli.net/article30/ipscpo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、品牌網(wǎng)站設(shè)計(jì)、動(dòng)態(tài)網(wǎng)站、用戶體驗(yàn)、網(wǎng)站導(dǎo)航、服務(wù)器托管

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

MySQL之SQL基礎(chǔ)