blog
blog copied to clipboard
Published
20 hours ago •
plh97
plh97
[SQL] 基础语法
MySQL - 机器信息
- Macbook 13寸 Pro 版
- OS - 10.14.1
安装MySQL
brew install mysql
装好后应该能在系统里面看到这个图标
MySQY - GUI界面工具
Navicat for MySQL 密码账号,以及端口一般默认就是3306
相关命令(注意一定要以分号结尾,不然会车默认就是换行)
展示有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| vmei |
+--------------------+
5 rows in set (0.01 sec)
创建数据库
mysql> CREATE DATABASES tests;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASES tests' at line 1
mysql> CREATE DATABASE tests;
Query OK, 1 row affected (0.02 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tests |
| vmei |
+--------------------+
6 rows in set (0.00 sec)
使用X数据库
mysql> USE vmei;
Database changed
创建某个表
CREATE TABLE cats
(
id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
name VARCHAR(150) NOT NULL, # Name of the cat
owner VARCHAR(150) NOT NULL, # Owner of the cat
birth DATE NOT NULL, # Birthday of the cat
PRIMARY KEY (id) # Make the id the primary key
);
使用某个表
mysql> SHOW TABLES;
+----------------+
| Tables_in_vmei |
+----------------+
| cats |
+----------------+
1 row in set (0.00 sec)
查看某个表的描述 describe
mysql> DESCRIBE cats;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| owner | varchar(150) | NO | | NULL | |
| birth | date | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
插入数据
INSERT INTO cats ( name, owner, birth) VALUES
( 'Sandy', 'Lennon', '2015-01-03' ),
( 'Cookie', 'Casey', '2013-11-13' ),
( 'Charlie', 'River', '2016-05-21' );
获取cat表里面所有数据
mysql> SELECT * FROM cats;
+----+---------+---------+------------+
| id | name | owner | birth |
+----+---------+---------+------------+
| 1 | peng | liheng | 2015-11-11 |
| 2 | chen | caifeng | 2015-11-12 |
| 3 | chen | caifeng | 2015-11-12 |
| 4 | hu | gangyu | 2015-11-13 |
| 5 | Sandy | Lennon | 2015-01-03 |
| 6 | Cookie | Casey | 2013-11-13 |
| 7 | Charlie | River | 2016-05-21 |
+----+---------+---------+------------+
7 rows in set (0.00 sec)
查询表里面某个键值得所有数据
mysql> SELECT name FROM cats;
+---------+
| name |
+---------+
| peng |
| chen |
| chen |
| hu |
| Sandy |
| Cookie |
| Charlie |
+---------+
7 rows in set (0.00 sec)
根据条件查询某个键值所有数据
mysql> SELECT name FROM cats WHERE owner = 'Casey';
+--------+
| name |
+--------+
| Cookie |
+--------+
1 row in set (0.00 sec)
在表里面删除某个值
mysql> DELETE FROM cats WHERE name = 'hu';
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM catS;
+----+---------+---------+------------+
| id | name | owner | birth |
+----+---------+---------+------------+
| 1 | peng | liheng | 2015-11-11 |
| 2 | chen | caifeng | 2015-11-12 |
| 3 | chen | caifeng | 2015-11-12 |
| 5 | Sandy | Lennon | 2015-01-03 |
| 6 | Cookie | Casey | 2013-11-13 |
| 7 | Charlie | River | 2016-05-21 |
+----+---------+---------+------------+
6 rows in set (0.00 sec)
插入某一列数据,再删除这一列数据
mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM cats;
+----+---------+--------+---------+------------+
| id | name | gender | owner | birth |
+----+---------+--------+---------+------------+
| 1 | peng | NULL | liheng | 2015-11-11 |
| 2 | chen | NULL | caifeng | 2015-11-12 |
| 3 | chen | NULL | caifeng | 2015-11-12 |
| 5 | Sandy | NULL | Lennon | 2015-01-03 |
| 6 | Cookie | NULL | Casey | 2013-11-13 |
| 7 | Charlie | NULL | River | 2016-05-21 |
+----+---------+--------+---------+------------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE cats DROP gender;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM cats;
+----+---------+---------+------------+
| id | name | owner | birth |
+----+---------+---------+------------+
| 1 | peng | liheng | 2015-11-11 |
| 2 | chen | caifeng | 2015-11-12 |
| 3 | chen | caifeng | 2015-11-12 |
| 5 | Sandy | Lennon | 2015-01-03 |
| 6 | Cookie | Casey | 2013-11-13 |
| 7 | Charlie | River | 2016-05-21 |
+----+---------+---------+------------+
6 rows in set (0.00 sec)
添加两列数据
mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE cats ADD what? CHAR(1) AFTER name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? CHAR(1) AFTER name' at line 1
mysql> ALTER TABLE cats ADD what CHAR(1) AFTER name;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE cats;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(150) | NO | | NULL | |
| what | char(1) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| owner | varchar(150) | NO | | NULL | |
| birth | date | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM cats;
+----+---------+------+--------+---------+------------+
| id | name | what | gender | owner | birth |
+----+---------+------+--------+---------+------------+
| 1 | peng | NULL | NULL | liheng | 2015-11-11 |
| 2 | chen | NULL | NULL | caifeng | 2015-11-12 |
| 3 | chen | NULL | NULL | caifeng | 2015-11-12 |
| 5 | Sandy | NULL | NULL | Lennon | 2015-01-03 |
| 6 | Cookie | NULL | NULL | Casey | 2013-11-13 |
| 7 | Charlie | NULL | NULL | River | 2016-05-21 |
+----+---------+------+--------+---------+------------+
6 rows in set (0.00 sec)
查询表格 cinema
表格如下
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
它的输出:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
条件:
- id是奇数
- description != boring
- 最后输出的表格,按照
rating进行递减排序
SELECT * FROM cinema WHERE (id % 2 = 1) AND (description != 'boring') ORDER BY rating DESC;
关联查询
-
left join 以 table1 为主表 左交集
select * from table1 left join table2 on table1.id=table2.id having table1.age>2 -
right join 以 right join 进来的为主表, 右交集
-
inner join 并集
合并两个查询
- union
- union all
查询条件
- group by <condition 1>,
...
排序
- order by <asc 增序/ desc 降序>
限制查询个数 <限制一条>
- limit by 1
性能优化
单个表查询性能优于内嵌表, 优于 left join
