文章目录
- 0. 推荐书 << sql必知必会>>
- 1. 安装好mysql后, 进入mysql
- 2. 显示所有数据库
- 3. 创建数据库
- 4. 选定使用某个数据库
- 5. 显示数据库的表
- 6. 查看表的列信息
- 7. 从表table_1中选定所有列
- 8. 选出其中的两列
- 9. 新建一个表(增)
- 10. 删除数据表 (删)
- 11. 插入一行到表中(增)
- 12. 更新特定行的特定列(改)
- 13. 删除某一或所有行(删)
- 14. 三种注释
- 15. 数据排序
- 16. 使用where过滤数据
- 17. 高级过滤(and, or, in, not)
- 18. 使用%, _通配符
- 19. 使用union组合查询
- 20. 插入部分行
- 21. 插入选中的行
- 22. 函数
- 23. 数据类型
0. 推荐书 << sql必知必会>>
1. 安装好mysql后, 进入mysql
mysql -u root -p
2. 显示所有数据库
SHOW DATABASES;
3. 创建数据库
create database studydb_2;
4. 选定使用某个数据库
USE studydb;
5. 显示数据库的表
show tables;
6. 查看表的列信息
desc table_1;
7. 从表table_1中选定所有列
select * from table_1;
8. 选出其中的两列
select name, url from table_1;
9. 新建一个表(增)
新建表比较麻烦, 因为需要指定有哪些行, 行的数据类型, 主键, 编码等等. 下面是一个例子.
CREATE TABLE IF NOT EXISTS `table_2`(
joke varchar(255),
name varchar(100) not null,
id int,
time date,
primary key (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
10. 删除数据表 (删)
drop table table_2;
11. 插入一行到表中(增)
insert into table_2 values( '测试', 'hello world', 110, '2019-7-3');
例子
mysql> desc table_2; +-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| joke | varchar(255) | YES | | NULL | |
| name | varchar(100) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| time | date | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into table_2 values( '测试', 'hello world', 110, '2019-7-3');
Query OK, 1 row affected (0.01 sec)
mysql> select * from table_2;
+--------+-------------+-----+------------+
| joke | name | id | time |
+--------+-------------+-----+------------+
| 测试 | hello world | 110 | 2019-07-03 |
+--------+-------------+-----+------------+
1 row in set (0.00 sec)
12. 更新特定行的特定列(改)
update table_2
set name = '我的名字'
where id = 1110;
结果
mysql> select * from table_2;
+---------+-------------+------+------------+
| joke | name | id | time |
+---------+-------------+------+------------+
| 测试 | hello world | 110 | 2019-07-03 |
| 测试2 | hefadfald | 1110 | 2019-07-03 |
+---------+-------------+------+------------+
2 rows in set (0.00 sec)
mysql> update table_2
-> set name = '我的名字'
-> where id = 1110;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from table_2;
+---------+--------------+------+------------+
| joke | name | id | time |
+---------+--------------+------+------------+
| 测试 | hello world | 110 | 2019-07-03 |
| 测试2 | 我的名字 | 1110 | 2019-07-03 |
+---------+--------------+------+------------+
2 rows in set (0.00 sec)
mysql>
13. 删除某一或所有行(删)
delete from table_2
where id = 1110;
mysql> delete from table_2
-> where id = 1110;
Query OK, 1 row affected (0.00 sec)
mysql> select * from table_2;
+--------+-------------+-----+------------+
| joke | name | id | time |
+--------+-------------+-----+------------+
| 测试 | hello world | 110 | 2019-07-03 |
+--------+-------------+-----+------------+
1 row in set (0.00 sec)
mysql>
14. 三种注释
mysql> /*hlajf*/ select *
-> -- 说什么
-> # 注释测试
-> from table_2;
+--------+-------------+-----+------------+
| joke | name | id | time |
+--------+-------------+-----+------------+
| 测试 | hello world | 110 | 2019-07-03 |
+--------+-------------+-----+------------+
1 row in set (0.00 sec)
mysql>
15. 数据排序
默认从小到大
select * from table_1 order by name, age;
指单行排序
mysql> select * from table_1 order by name;
+----------+------+----------------+
| name | age | url |
+----------+------+----------------+
| jack | 6 | www.cccccc.net |
| Jamey | 12 | www.baidu.com |
| JameyWoo | 20 | fiveplus.top |
| JameyWoo | 20 | fiveplus.top |
| JameyWoo | 20 | fiveplus.top |
| JameyWoo | 20 | fiveplus.top |
| JameyWoo | 20 | fiveplus.top |
| Tom | 21 | www.google.com |
+----------+------+----------------+
8 rows in set (0.00 sec)
指定多行排序, 先按url, 再按age排序.
mysql> select * from table_1;
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Jamey | 12 | www.baidu.com |
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
+-------+------+----------------+
5 rows in set (0.00 sec)
mysql> select * from table_1 order by url, age;
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Jamey | 12 | www.baidu.com |
| smile | 15 | www.baidu.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
| Tom | 21 | www.google.com |
+-------+------+----------------+
5 rows in set (0.00 sec)
mysql>
按列的序号来排序
mysql> select url, name, age from table_1 order by 2;
+----------------+-------+------+
| url | name | age |
+----------------+-------+------+
| www.cccccc.net | jack | 6 |
| www.baidu.com | Jamey | 12 |
| www.baidu.com | smile | 15 |
| www.google.com | timy | 12 |
| www.google.com | Tom | 21 |
+----------------+-------+------+
5 rows in set (0.00 sec)
mysql>
按降序(从大到小)排序
mysql> select url, name, age from table_1 order by 2 desc;
+----------------+-------+------+
| url | name | age |
+----------------+-------+------+
| www.google.com | Tom | 21 |
| www.google.com | timy | 12 |
| www.baidu.com | smile | 15 |
| www.baidu.com | Jamey | 12 |
| www.cccccc.net | jack | 6 |
+----------------+-------+------+
5 rows in set (0.01 sec)
mysql>
16. 使用where过滤数据
mysql> select name, url from table_1 where url = 'www.baidu.com';
+-------+---------------+
| name | url |
+-------+---------------+
| Jamey | www.baidu.com |
| smile | www.baidu.com |
+-------+---------------+
2 rows in set (0.00 sec)
mysql>
运算符过滤
mysql> select * from table_1 where age < 13;
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Jamey | 12 | www.baidu.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
+-------+------+----------------+
3 rows in set (0.00 sec)
mysql>
图片来自 <<sql必知必会(第4版)>>
17. 高级过滤(and, or, in, not)
需要注意优先级的问题
mysql> select * from table_1 where age < 13 and url <> 'www.baidu.com' or name = 'Tom';
+------+------+----------------+
| name | age | url |
+------+------+----------------+
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
+------+------+----------------+
3 rows in set (0.00 sec)
使用in操作符
mysql> select * from table_1 where age in (6, 21, 13);
+------+------+----------------+
| name | age | url |
+------+------+----------------+
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
+------+------+----------------+
2 rows in set (0.00 sec)
使用not操作符
mysql> select * from table_1 where age not in (6, 21, 13);
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Jamey | 12 | www.baidu.com |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
+-------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from table_1 where not age in (6, 21, 13);
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Jamey | 12 | www.baidu.com |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
+-------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from table_1 where age in not (6, 21, 13);
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 'not (6, 21, 13)' at line 1
mysql> select * from table_1 not where age in not (6, 21, 13);
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 'not where age in not (6, 21, 13)' at line 1
mysql>
18. 使用%, _通配符
可以设置搜索是否区分大小写
%的使用
mysql> select * from table_1 where name like 't%';
+--------+------+----------------+
| name | age | url |
+--------+------+----------------+
| Tom | 21 | www.google.com |
| timy | 12 | www.google.com |
| tohaha | 17 | blog.csdn.net |
+--------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from table_1 where name = 'tom';
+------+------+----------------+
| name | age | url |
+------+------+----------------+
| Tom | 21 | www.google.com |
+------+------+----------------+
1 row in set (0.01 sec)
mysql>
_ 的使用
_ 用来匹配单个字符
mysql> select * from table_1 where name like 't_m';
+------+------+----------------+
| name | age | url |
+------+------+----------------+
| Tom | 21 | www.google.com |
+------+------+----------------+
1 row in set (0.00 sec)
mysql>
19. 使用union组合查询
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,
各个列不需要以相同的次序列出)。
mysql> select * from table_1 where name like 't_m'
-> union
-> select * from table_1 where name like
-> 'j%';
+-------+------+----------------+
| name | age | url |
+-------+------+----------------+
| Tom | 21 | www.google.com |
| Jamey | 12 | www.baidu.com |
| jack | 6 | www.cccccc.net |
+-------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from table_1 where name like 't_m' union select url from table_1 where name like 'j%';
ERROR 1222 (21000): The used SELECT statements have a different number of columns
列不需要相同, 但这样的话, 似乎数据就乱了.
mysql> select url, age, name from table_1 where age = 6 union select name, age, url from table_1 where name = "tom";
+----------------+------+----------------+
| url | age | name |
+----------------+------+----------------+
| www.cccccc.net | 6 | jack |
| Tom | 21 | www.google.com |
+----------------+------+----------------+
2 rows in set (0.01 sec)
mysql>
20. 插入部分行
未插入的为NULL
mysql> insert into table_1(name, age)
-> values('test_2', 100);
Query OK, 1 row affected (0.01 sec)
mysql> select * from table_1;
+--------+------+----------------+
| name | age | url |
+--------+------+----------------+
| Jamey | 12 | www.baidu.com |
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
| tohaha | 17 | blog.csdn.net |
| test_2 | 100 | NULL |
+--------+------+----------------+
7 rows in set (0.00 sec)
mysql>
21. 插入选中的行
从另一个表中选中部分数据插入该表
mysql> select * from table_2;
+-------+------+
| name | age |
+-------+------+
| name1 | 11 |
| name2 | 12 |
+-------+------+
2 rows in set (0.00 sec)
mysql> select * from table_1;
+--------+------+----------------+
| name | age | url |
+--------+------+----------------+
| Jamey | 12 | www.baidu.com |
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
| tohaha | 17 | blog.csdn.net |
| test_2 | 100 | NULL |
+--------+------+----------------+
7 rows in set (0.00 sec)
mysql> insert into table_1(name, age)
-> select name, age from table_2 where age < 12;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from table_1;
+--------+------+----------------+
| name | age | url |
+--------+------+----------------+
| Jamey | 12 | www.baidu.com |
| Tom | 21 | www.google.com |
| jack | 6 | www.cccccc.net |
| timy | 12 | www.google.com |
| smile | 15 | www.baidu.com |
| tohaha | 17 | blog.csdn.net |
| test_2 | 100 | NULL |
| name1 | 11 | NULL |
+--------+------+----------------+
8 rows in set (0.00 sec)
mysql>
22. 函数
图片截自<<sql必知必会>>
文本处理函数
日期和时间处理函数
数值处理函数
23. 数据类型
图片截自<<sql必知必会>>
字符串类型