mysql語法

登陸資料庫

mysql -u root -p (回車)
（密碼）

SQL語句種類

1. DDL（資料定義語言）

檢視所有資料庫

mysql> show databases;

執行結果
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

建立資料庫

create table 庫名；
mysql> create database aa; //建立叫做aa的資料庫

mysql> show databases; //再次檢視所有的資料庫，多了個aa的庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aa                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+

刪除資料庫

dorp database 要刪除的庫名;

mysql> drop database aa; //刪除aa的庫
mysql> show databases;  //aa已經被刪除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

使用資料庫

use 庫名

mysql> use aa;  
Database changed

建立表，檢視錶，刪除表

 create table 表名(欄位1 型別,欄位2 型別......);

 mysql> create table bb(id int,xm varchar(20));//建立bb的表，裡面建立2個欄位
 mysql> show tables;  //檢視當前庫裡所有的表
 +--------------+
| Tables_in_aa |
+--------------+
| bb           |
+--------------+

mysql> desc bb;//檢視裡面的內容
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| xm    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> drop table bb;//刪除表

只刪除欄位

alert table 表名 drop 要刪除的欄位;

mysql> alter table bb drop xm;
mysql> desc bb;//結果
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

新增表裡面的欄位

alter table 表名 add 欄位 欄位型別；

mysql> alter table bb add xh varchar(20);
mysql> desc bb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| xh    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改欄位名和屬性

alter table 表名 change 欄位 要修改的欄位 型別; 

mysql> alter table bb change xh xm int;
mysql> desc bb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| xm    | int(11) | YES  |     | NULL    |       |

2. DML（資料操作語言）

插入語句INSERT INTO

insert into 插入的表（欄位1，欄位2,...） values (值1，值2,...);

mysql> insert into aa (username,password) values ('admin','root');

查詢語句

select * from 表名;  //*萬用字元

mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
+----------+----------+
··················································································
也可以
select 要查詢的欄位 from 表名; 

mysql> select username from aa;
+----------+
| username |
+----------+
| admin    |
+----------+

多插入幾個值
mysql> select * from aa;
+-----------+----------+
| username  | password |
+-----------+----------+
| admin1111 | root     |
| admin     | 123456   |
| admin     | aaaaaa   |
| admin     | bbbbbb   |
+-----------+----------+
增加條件查詢
mysql> select * from aa where username='admin';查詢username='admin'的值
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

更新資料

update 表名 set 要修改的欄位=要更新的資料

mysql> update aa set username='admin';//將username裡面的資料全部更新為admin
mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

如果只更新一條，增加條件即可
mysql> update aa set 
username='admin1111' where password='root';//更新username欄位，條件password等於root
mysql> select * from aa;
+-----------+----------+
| username  | password |
+-----------+----------+
| admin1111 | root     |
| admin     | 123456   |
| admin     | aaaaaa   |
| admin     | bbbbbb   |
+-----------+----------+

刪除資料

delete from 表名 where 欄位=‘值’

mysql> delete from aa where username='admin1111'; //刪除username欄位為admin1111的資料
mysql> select * from aa;
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

3. DCL（資料控制語言）

排序

select*from 表 order by 欄位 ASC(/DESC) //ASC（預設）升序，DESC降序

mysql> select * from aa; 
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     | //預設排序
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql>select * from aa order by username DESC;//倒序
+----------+------------+
| username | password   |
+----------+------------+
| admin4   | 1234567890 |
| admin3   | 123456789  |
| admin2   | 12345678   |
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
+----------+------------+

limit分頁

select * from aa limit n,m; //n表示從第幾行開始，m表示取幾條


mysql> select * from aa;
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa limit 0,2;//從第一行開始取2條資料
+----------+----------+
| username | password |
+----------+----------+
| admin1   | 1234567  |
| admin    | 123456   |
+----------+----------+
mysql> select * from aa limit 0,1;/從第一行開始取1條資料
+----------+----------+
| username | password |
+----------+----------+
| admin1   | 1234567  |
+----------+----------+
mysql> select * from aa limit 3,2;//從第四行開始取2條資料
+----------+----------+
| username | password |
+----------+----------+
| admin    | bbbbbb   |
| admin2   | 12345678 |
+----------+----------+
2 rows in set (0.00 sec)

模糊查詢

select * from 表 where 欄位 like 值; //like跟等於差不多，like像的意思

mysql> select * from aa where username like 'admin';//把aa表裡面像admin的都取出來
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+


mysql> select * from aa where username like '%a%'; 
// %a%資料裡面有a的都取出來，如果是a%把開頭是a的取出來，%a把結尾是a的取出來
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa where password like '%8';//結尾是8的取出來
+----------+----------+
| username | password |
+----------+----------+
| admin2   | 12345678 |
+----------+----------+

運算子

運算子號 + - * / %
邏輯運算子 and(&&) not(!!) or(||)

算術運算

mysql> select 9+1;//加
+-----+
| 9+1 |
+-----+
|  10 |
+-----+

mysql> select 9-1;//減
+-----+
| 9-1 |
+-----+
|   8 |
+-----+

mysql> select 9*2; //乘
+-----+
| 9*2 |
+-----+
|  18 |
+-----+

mysql> select 9/3;//除
+--------+
| 9/3    |
+--------+
| 3.0000 |
+--------+

mysql> select 9%3;//取於
+------+
| 9%3  |
+------+
|    0 |
+------+

邏輯運算

mysql> select * from aa;
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

mysql> select * from aa where username = 'admin' and password=123456;
//查詢username欄位等於admin，並且password等於123456的資料
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
+----------+----------+

mysql> select * from aa where username = 'admin' or password='aaaaaa';
//查詢usernam等於admin或password等於aaaaaa的資料
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+

mysql> select * from aa where not username = 'admin';
//查詢username裡不等於admin的資料
+----------+------------+
| username | password   |
+----------+------------+
| admin1   | 1234567    |
| admin2   | 12345678   |
| admin3   | 123456789  |
| admin4   | 1234567890 |
+----------+------------+

聯合查詢

union

mysql> select * from aa where username='admin' union select * from aa where username='admin4';
//將2個查詢到的合在一起輸出，可以是不同的表，但是欄位必須相同
+----------+------------+
| username | password   |
+----------+------------+
| admin    | 123456     |
| admin    | aaaaaa     |
| admin    | bbbbbb     |
| admin4   | 1234567890 |
+----------+------------+

子查詢

select * from user where username = (select username from admin where id=1);
//admin表當id=1的使用者名稱是否在user表也存在

mysql> select * from aa where username=(select 'admin');
//相當於select * from aa where username='admin'
+----------+----------+
| username | password |
+----------+----------+
| admin    | 123456   |
| admin    | aaaaaa   |
| admin    | bbbbbb   |
+----------+----------+