sql 基础--mysql 5
mysql5.6 安装 http://jingyan.baidu.com/article/f3ad7d0ffc061a09c3345bf0.html
md中输入:net start mysql,服务启动成功
mysql -u root -p（第一次登录没有密码，直接按回车过）,登录成功！
1.使用database
show databases;
use t_database;
show tables;
show columns from pw_luck;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 28 rows in set (0.00 sec)
2.查询
单列：select name from pw_luck;
多列：select uid,name from pw_luck;
所有：select * from pw_luck;
distinct关键字（去重）：select distinct name from pw_luck;
注意：用在多个列上表示多个列值都一样才会被去重
mysql> select * from pw_luck; +-----+-----------+------+ | uid | name | msg | +-----+-----------+------+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | | 2 | lisi | 1001 | | 3 | wang5 | 1001 | | 7 | zhangsan7 | 1000 | | 8 | zhangsan8 | 1000 | | 9 | zhangsan9 | 1000 | | 10 | wang5 | 1000 | +-----+-----------+------+ 8 rows in set (0.00 sec) mysql> select distinct name from pw_luck; +-----------+ | name | +-----------+ | Wilson | | zhangsan | | lisi | | wang5 | | zhangsan7 | | zhangsan8 | | zhangsan9 | +-----------+ 7 rows in set (0.00 sec) mysql> select distinct name,msg from pw_luck; +-----------+------+ | name | msg | +-----------+------+ | Wilson | 100 | | zhangsan | 100 | | lisi | 1001 | | wang5 | 1001 | | zhangsan7 | 1000 | | zhangsan8 | 1000 | | zhangsan9 | 1000 | | wang5 | 1000 | +-----------+------+ 8 rows in set (0.00 sec)
想要得到多个列的结果且要对某一列去重 需要用到 group by 函数
mysql> select name,msg from pw_luck group by name; +-----------+------+ | name | msg | +-----------+------+ | lisi | 1001 | | wang5 | 1001 | | Wilson | 100 | | zhangsan | 100 | | zhangsan7 | 1000 | | zhangsan8 | 1000 | | zhangsan9 | 1000 | +-----------+------+ 7 rows in set (0.00 sec)
限制结果：limit 2 表示 0-2行 limit 2，2 表示2行开始长度2行
如 select * from pw_luck limit 2,2
mysql> select * from pw_luck limit 2; +-----+----------+-----+ | uid | name | msg | +-----+----------+-----+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | +-----+----------+-----+ 2 rows in set (0.00 sec) mysql> select * from pw_luck limit 2,2; +-----+-------+------+ | uid | name | msg | +-----+-------+------+ | 2 | lisi | 1001 | | 3 | wang5 | 1001 | +-----+-------+------+ 2 rows in set (0.00 sec)
3.排序
数据排序 select * from pw_luck order by name
多行排序 select * from pw_luck order by name，msg
升序，降序排列 select * from pw_luck order by name desc(降序)
select * from pw_luck order by name asc（升序，默认不写也可以），msg desc
4.where 语句 过滤数据
mysql> select * from pw_luck where name='wang5'; +-----+-------+------+ | uid | name | msg | +-----+-------+------+ | 3 | wang5 | 1001 | | 10 | wang5 | 1000 | +-----+-------+------+ 2 rows in set (0.00 sec)
不匹配检查：<>
mysql> select * from pw_luck where msg <> 1000; +-----+----------+------+ | uid | name | msg | +-----+----------+------+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | | 2 | lisi | 1001 | | 3 | wang5 | 1001 | +-----+----------+------+ 4 rows in set (0.00 sec)
between ... and... 范围检查
mysql> select * from pw_luck where msg between 1 and 1000; +-----+-----------+------+ | uid | name | msg | +-----+-----------+------+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | | 7 | zhangsan7 | 1000 | | 8 | zhangsan8 | 1000 | | 9 | zhangsan9 | 1000 | | 10 | wang5 | 1000 | +-----+-----------+------+ 6 rows in set (0.00 sec)
null 空置检查
select * from pw_luck where name is not null;
select * from pw_luck where name is null;
mysql> select * from pw_luck where name is not null; +-----+-----------+------+ | uid | name | msg | +-----+-----------+------+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | | 2 | lisi | 1001 | | 3 | wang5 | 1001 | | 7 | zhangsan7 | 1000 | | 8 | zhangsan8 | 1000 | | 9 | zhangsan9 | 1000 | | 10 | wang5 | 1000 | +-----+-----------+------+ 8 rows in set (0.00 sec)
5.组合语句：
and or 操作符
select * from pw_luck where name is not null and msg <2000;
select * from pw_luck where name = 'wang5'or msg <1000;
and 计算优先级别高于 or 必要时候加上括号
mysql> select * from pw_luck where name = 'wang5'or msg <1000 and uid >3; +-----+-------+------+ | uid | name | msg | +-----+-------+------+ | 3 | wang5 | 1001 | | 10 | wang5 | 1000 | +-----+-------+------+ 2 rows in set (0.10 sec) mysql> select * from pw_luck where (name = 'wang5'or msg <1000 ) and uid >3; +-----+-------+------+ | uid | name | msg | +-----+-------+------+ | 10 | wang5 | 1000 | +-----+-------+------+ 1 row in set (0.01 sec)
in 操作符
select * from pw_luck where msg in (1000,1001);
not 操作符:否定气候的任何条件
mysql> select * from pw_luck where msg not in (1000,1001); +-----+----------+-----+ | uid | name | msg | +-----+----------+-----+ | 0 | Wilson | 100 | | 1 | zhangsan | 100 | +-----+----------+-----+ 2 rows in set (0.00 sec)
6.通配符过滤 like
%任何字符出现的次数(不匹配null)
_任何单个字符
mysql> select * from pw_luck where name like 'wang%' or name like 'zhang%';
+-----+-----------+------+
| uid | name | msg |
+-----+-----------+------+
| 1 | zhangsan | 100 |
| 3 | wang5 | 1001 |
| 7 | zhangsan7 | 1000 |
| 8 | zhangsan8 | 1000 |
| 9 | zhangsan9 | 1000 |
| 10 | wang5 | 1000 |
| 11 | wang5% | 2000 |
+-----+-----------+------+
7 rows in set (0.00 sec)
escape 查询条件中包含通配符用 escape 过滤通配符
如下：M代表转字符将后面的%转义为普通% 等价于 搜索 包含wang5%的条件
mysql> select * from pw_luck where name like 'wang5M%' escape 'M'; +-----+--------+------+ | uid | name | msg | +-----+--------+------+ | 11 | wang5% | 2000 | +-----+--------+------+ 1 row in set (0.00 sec)
