1、MySQL在DOS窗口进入
1)设置环境变量:Path添加MySQL运行路径(C:\Program Files\MySQL\MySQL Server 5.7\bin)
mysql -u root -p cms -h 127.0.0.1 -P 3306
2、show命令
show databases; //显示所有数据库
use cms; //进入某个数据库
show tables; //显示某个数据库下的所有表
describe t_admin;
show columns from t_admin; //显示某张表下的所有列字段信息
show status; //显示服务器状态信息
show create database database_name; //显示创建数据库的语句
show create table table_name form database db_name; //显示创建表的语句
show grants; //显示授予用户的安全权限
show errors; //显示服务器错误信息
show warnings; //显示服务器警告消息
show databases; //显示所有数据库
use cms; //进入某个数据库
show tables; //显示某个数据库下的所有表
describe t_admin;
show columns from t_admin; //显示某张表下的所有列字段信息
show status; //显示服务器状态信息
show create database database_name; //显示创建数据库的语句
show create table table_name form database db_name; //显示创建表的语句
show grants; //显示授予用户的安全权限
show errors; //显示服务器错误信息
show warnings; //显示服务器警告消息
3、检索列通配符使用规则:除非你确实需要使用表中的所有列,否则最好不用使用【*】通配符,这样检索不需要的列会降低检索和应用程序的性能
4、distinct:去除重复(SELECT DISTINCT 列名称, 列名称 FROM 表名称)
select distinct name from A // 对字段name去重
select distinct id, name from A // 对字段id、name合集去重
select distinct name from A // 对字段name去重
select distinct id, name from A // 对字段id、name合集去重
5、limit:限制结果:默认从第0行开始
select prod_name from products limit 5; // 从第0行开始,取出五行记录
select prod_name from products limit 5, 5 //从第5行开始,取出5行记录
select * from t_activity_order limit 4 offset 3; //从第3行开始,取出4行记录 == select * from t_activity_order limit 3, 4
select prod_name from products limit 5; // 从第0行开始,取出五行记录
select prod_name from products limit 5, 5 //从第5行开始,取出5行记录
select * from t_activity_order limit 4 offset 3; //从第3行开始,取出4行记录 == select * from t_activity_order limit 3, 4
6、空值NULL检查:在过滤中选择不具有特定值的行时,可能希望返回具有NULL值的行,但是不行,因为未知具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或者不匹配过滤的时候不返回它们
select * from t_admin where name != 'admin' //返回所有name不为admin的列,但如果name列中如果存在NULL的行,则不会返回
select * from t_admin where name != 'admin' //返回所有name不为admin的列,但如果name列中如果存在NULL的行,则不会返回
7、通配符:like
%:代表任意字符(包含0-无穷个)
_:代表一个字符
通配符使用原则:1)不要过度使用通配符,搜索效率很低。如果其他操作符能达到相同的目的,应该使用其他操作符
2)避免将通配符放在搜索条件的开始处,这样搜索起来最慢
8、正则表达式:字段 regexp '正则表达式'
- regexp和like区别:like是整行匹配,regexp是对行内的内容进行匹配
- regexp匹配不区分大小写,如果需要区分大小写,使用regxrep binary关键字
正则匹配规则:
1). :匹配任意一个字符
2)| :直接or操作(1|2|3匹配1或者2或者3)
[]:匹配几个字符中的一个([1-578]匹配1、2、3、4、5、7、8中的一个)发现使用[123]和(1|2|3)和[1|2|3]作用一样
4)^:如果在开始位置,表示匹配以其后面字符为开始,如果在中间表示非
5)-:[a-zA-Z0-9]
6)匹配特殊字符:前面加\\
7)*:匹配0个或者多个
等价于{1,})
9)?:匹配0个或者1个(等价于{0,1})
10){n}:指定n个数目的匹配
11){n, }:指定不少于指定数目的匹配
12){n,m}:匹配数目的范围(m不超过255)
13)$:文本的结尾
14)[[:<:]]:词的开始
15)[[:>:]]:词的结尾
mysql> select prod_id, prod_name from products where prod_name regexp '.000';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.08 sec)
mysql> select prod_id, prod_name from products where prod_name regexp 'detonator';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| DTNTR | Detonator |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp binary 'detonator';
Empty set (0.04 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '1000|2000|3000';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[123] ton';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[^123] ton';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
+---------+--------------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '(1|2|3) ton';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select prod_id,note_date from productnotes where note_date regexp '2005-0(8|9)-[01]';
+---------+---------------------+
| prod_id | note_date |
+---------+---------------------+
| TNT2 | 2005-08-17 00:00:00 |
| OL1 | 2005-08-18 00:00:00 |
| SAFE | 2005-08-18 00:00:00 |
| FC | 2005-08-19 00:00:00 |
| ANV03 | 2005-09-01 00:00:00 |
| FC | 2005-09-01 00:00:00 |
| SLING | 2005-09-02 00:00:00 |
| SAFE | 2005-09-02 00:00:00 |
| ANV01 | 2005-09-05 00:00:00 |
| SAFE | 2005-09-07 00:00:00 |
+---------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from vendors where vend_name regexp '\\.';
+---------+--------------+-----------------+-----------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
+---------+--------------+-----------------+-----------+------------+----------+--------------+
| 1004 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA |
+---------+--------------+-----------------+-----------+------------+----------+--------------+
1 row in set (0.00 sec)
mysql> select * from products where prod_name regexp '\\([0-9] stick?\\)';
+---------+---------+---------------+------------+------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------+------------+------------------------+
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
+---------+---------+---------------+------------+------------------------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[0-9]{4}';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
或者
mysql> select prod_id, prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]';
mysql> select prod_id, prod_name from products where prod_name regexp '^[0-9\\.]';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
3 rows in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '.000';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.08 sec)
mysql> select prod_id, prod_name from products where prod_name regexp 'detonator';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| DTNTR | Detonator |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp binary 'detonator';
Empty set (0.04 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '1000|2000|3000';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[123] ton';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[^123] ton';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
+---------+--------------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '(1|2|3) ton';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
mysql> select prod_id,note_date from productnotes where note_date regexp '2005-0(8|9)-[01]';
+---------+---------------------+
| prod_id | note_date |
+---------+---------------------+
| TNT2 | 2005-08-17 00:00:00 |
| OL1 | 2005-08-18 00:00:00 |
| SAFE | 2005-08-18 00:00:00 |
| FC | 2005-08-19 00:00:00 |
| ANV03 | 2005-09-01 00:00:00 |
| FC | 2005-09-01 00:00:00 |
| SLING | 2005-09-02 00:00:00 |
| SAFE | 2005-09-02 00:00:00 |
| ANV01 | 2005-09-05 00:00:00 |
| SAFE | 2005-09-07 00:00:00 |
+---------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from vendors where vend_name regexp '\\.';
+---------+--------------+-----------------+-----------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
+---------+--------------+-----------------+-----------+------------+----------+--------------+
| 1004 | Furball Inc. | 1000 5th Avenue | New York | NY | 11111 | USA |
+---------+--------------+-----------------+-----------+------------+----------+--------------+
1 row in set (0.00 sec)
mysql> select * from products where prod_name regexp '\\([0-9] stick?\\)';
+---------+---------+---------------+------------+------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------+------------+------------------------+
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
+---------+---------+---------------+------------+------------------------+
1 row in set (0.00 sec)
mysql> select prod_id, prod_name from products where prod_name regexp '[0-9]{4}';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
或者
mysql> select prod_id, prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]';
mysql> select prod_id, prod_name from products where prod_name regexp '^[0-9\\.]';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
3 rows in set (0.00 sec)
9、可以在不使用数据库表的情况下,检查正则表达式是否正确,返回结果如果为0,则表示不匹配,如果返回1则表示匹配
mysql> select '中文' regexp '[0-9]';
+-------------------------+
| '中文' regexp '[0-9]' |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select '123wqwww' regexp '[0-9]';
+---------------------------+
| '123wqwww' regexp '[0-9]' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select '中文' regexp '[0-9]';
+-------------------------+
| '中文' regexp '[0-9]' |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select '123wqwww' regexp '[0-9]';
+---------------------------+
| '123wqwww' regexp '[0-9]' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
10、拼接字段
1)将值联接到一起形成单个值,在MySQL中使用concat()函数
mysql> select concat(vend_name, '(', vend_country, ')') from vendors;
+-------------------------------------------+
| concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> select concat(vend_name, '(', vend_country, ')') from vendors;
+-------------------------------------------+
| concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+-------------------------------------------+
6 rows in set (0.00 sec)
2)rtrim()、ltrim()、trim():去掉左右空格
mysql> select concat(rtrim(vend_name), '(', ltrim(vend_country), ')') from vendors;
+---------------------------------------------------------+
| concat(rtrim(vend_name), '(', ltrim(vend_country), ')') |
+---------------------------------------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+---------------------------------------------------------+
mysql> select concat(rtrim(vend_name), '(', ltrim(vend_country), ')') from vendors;
+---------------------------------------------------------+
| concat(rtrim(vend_name), '(', ltrim(vend_country), ')') |
+---------------------------------------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+---------------------------------------------------------+
3)使用别名:as
mysql> select concat(vend_name, '(', vend_country, ')') as vend_title from vendors;
+------------------------+
| vend_title |
+------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+------------------------+
6 rows in set (0.00 sec)
mysql> select concat(vend_name, '(', vend_country, ')') as vend_title from vendors;
+------------------------+
| vend_title |
+------------------------+
| Anvils R Us(USA) |
| LT Supplies(USA) |
| ACME(USA) |
| Furball Inc.(USA) |
| Jet Set(England) |
| Jouets Et Ours(France) |
+------------------------+
6 rows in set (0.00 sec)
4)执行算术运算:+-*/
mysql> select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.01 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2015-04-20 22:54:25 |
+---------------------+
1 row in set (0.00 sec)
mysql> select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems where order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.01 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2015-04-20 22:54:25 |
+---------------------+
1 row in set (0.00 sec)