MySQL SQL使用技巧

plantegg

　　须知：
　　SQL语言：结构化查询语言，是关系型数据库查询语言的标准，不同的数据库虽然有自己私有扩展，但关键词都支持：（select、update、delete、insert、where）
　　SQL语句分类：像Oracle、MSSQL都是通用的
　　DDL：数据定义语言（create、alter、drop、rename）
　　DML：数据库维护语言（select、insert、update、delete）
　　DCL：数据库控制语言，权限（Grant、revoke）
　　TCL：事务控制语言（commt、sarepqint）：新型的语句
　　一、数据库增删改查
　　根据这个表结构以下来操作:
　　mysql> select * from user;
　　+----+----------+------+

　　|>　　+----+----------+------+
　　基本操作
　　1.创建数据库
　　create database name;
　　2.删除数据库
　　drop database name;
　　3.创建一个用户表
　　create table user(id int,name varchar(30),pass varchar(30));
　　4.插入数据
　　insert into user(id,name,pass) values("1","zhangsan","123");
　　5.删除一个表
　　drop table user;
　　6.查看表字段
　　desc table user;
　　7.查看表数据
　　select * from user;
　　8.修改表名
　　rename table user to tab1;
　　9.更新数据
　　update user set pass="newpass" where pass=123;  #把密码更新为newpass

　　update user set>　　10.重命名字段名
　　alter table user change pass newname varchar(30);
　　11.表中添加一个字段
　　alter table user add age int;
　　12.修改字段
　　alter table user modify age int no null default 20; #当年龄输入为空时，默认为20岁
　　13.删除表中的一个字段
　　alter table user drop age;
　　14.删除一条记录

　　delete from user where>　　15.查找一条记录

　　select>　　16.删除ID记录

　　delete from user where>
　　delete from user where>
　　delete from user where>
　　delete from user where>　　高级查询
　　1.返回结果删除重复项

　　select distinct>　　2.查询字段中为NULL
　　select * from where pass is null;
　　select * from where pass is not null; #查询不为NULL的
　　3.like模糊查询,包含zhang的列出来
　　select * from user where name like '%zhang%';
　　select * from user where name like '%zhang%' or name like '%li%';#查找包含zhang或li的列出来
　　也可以使用正则表达式查询,生产环境一般不用的，因为查询慢，效果一样
　　select * from user where name regexp 'li';
　　4.使用order by对查询结果排序(升序/降序)，默认是升序

　　select>　　5.使用limit取出排名前三个

　　select * from user  order by>　　6.concat函数使用-字符串连接符

　　mysql> select>　　+----+----------+------+------------+

　　|>　　+----+----------+------+------------+
　　|  1 | zhangsan | 123  | 1_zhangsan |
　　|  2 | lisi     | 123  | 2_lisi     |
　　|  3 | zhaowu   | 123  | 3_zhaowu   |
　　+----+----------+------+------------+
　　7.rand函数随机排序，如随机抽取前三名
　　select * from user order by rand() limit 3;
　　8.count统计,如统计多少个id记录
　　select count(*) count from user;
　　#count为自定义显示查询结果后字段名，*为mysql优化后的查询方法，要比直接写入id效率高
　　select count(id) from user where name='zhangsan'; #统计zhangsan多少条记录
　　9.sum求和，如统计同消费了多少钱
　　select sum(id) from user where name='lisi';  #所有id数加在一起
　　10.avg平均数,如求班级平均分
　　select avg(id) from user;
　　11.max最大值，如得到一个最高分
　　select max(id) from user;
　　12.min最小值
　　select min(id) from user;
　　13.group by分组聚合
　　select name,count(id) from user group by name order by desc;
　　#使用count来聚合，基于name分组，再order by排序下（一般排名都使用分组聚合）
　　select name,count(id) count from user group by name having count>=3;
　　#查询前三名，having是对分组的结果进行筛选，这不能用where，count代表查询结果后显示的字段名
　　14.普通多表查询（前提：两个表必须有关系）
　　先创建两个有关系的表：
　　create table user(id int unsigned auto_increment primary key,name varchar(30),age int);
　　create table post(id int unsigned auto_increment primary key,uid int,title varchar(200),content text);
　　结果一：查询用户发的所有记录
　　select user.name,post.title,post.content from user,post where user.id=post.uid;
　　结果二：统计每个用户有多少个记录
　　select user.name,post.title,post.content count(id) from user,post where user.id=post.uid group by user.name;
　　15.联表查询（查询成绩）
　　mysql> select * from tb1;
　　+------+----------+

　　|>　　+------+----------+
　　|    1 | zhangsan |
　　|    2 | lisi     |
　　+------+----------+
　　mysql> select * from tb2;
　　+------+-------+

　　|>　　+------+-------+
　　|    1 |    80 |
　　|    2 |    81 |
　　+------+-------+
　　mysql> select tb1.name,tb2.score from tb1,tb2 where tb1.id = tb2.id;
　　+----------+-------+
　　| name     | score |
　　+----------+-------+
　　| zhangsan |    80 |
　　| lisi     |    81 |
　　+----------+-------+
　　二、表字段类型
　　1.数值
　　int(size)：整型，只能存整数数字，不能为空，默认允许输入null，也可以设置不允许写（not null）
　　float：浮点型，可以写入整数或浮点数
　　1.1字段属性
　　unsigned：无符号，全是整数
　　zerofill：与长度无关，不够3位时前面补0，默认看不见
　　null与not null：允许输入null和不允许输入
　　default：不允许null情况下，当输入空时，则使用默认值
　　auto_increment：一般自增ID
　　1.2示例
　　例如，设置id为自增：
　　create table user(id int unsigned auto_increment primary key,name varchar(30),pass varchar(30));
　　primary key：有auto_increment必须设置为主键索引，提供查询速度，再插入就不用写id列了，如果你写了，就以你写的为准，继续自增。
　　例如，创建一个表，当输入性别为空时，默认则为男：
　　create table user2(id int unsigned auto_increment primary key,name varchar(30),sex varchar(5) not null default "nan");
　　查看字段信息：
　　mysql> desc user2;
　　+-------+------------------+------+-----+---------+----------------+
　　| Field | Type             | Null | Key | Default | Extra          |
　　+-------+------------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(30)      | YES  |     | NULL    |                |
　　| sex   | varchar(5)       | NO   |     | nan     |                |
　　+-------+------------------+------+-----+---------+----------------+
　　3 rows in set (0.00 sec)
　　2.字符串
　　char(size）：占用size的字节，但查询速度快，最大支持255个字符
　　varchar(size)：存多少占多少，剩点空间，最大支持65535个字符
　　text：支持65535字节
　　longtext：支持42亿字节
　　3.日期类型（有的会使用int类型来存时间戳）
　　以下是官方说法占用的字节，但实际会有不符。
　　date：年月日，占用3个字节
　　time：时分秒，占用3个字节
　　datetime：年月日时分秒，占用8个字节
　　year：年，占用1个字节
　　三、用户管理
　　1.设置Mysql密码
　　方法一：set password=password（'newpass'）;
　　方法二：update user set password=password（'newpass'） where user='root';

　　方法三：grant all on *.* to 'root'@'localhost'>　　再刷新：flush privileges;
　　2.创建用户并授权

　　grant all privileges on *.* to 'user'@'localhost'>　　*.*：对应的是数据库/表的权限
　　localhost：只允许本地访问，也可以设置%为所有访问
　　3.撤销用户权限

　　revoke all privileges on *.* from 'root'@'localhost'>　　四、数据库字符集
　　1.查看服务器基本信息\s
123456789101112131415161718192021mysql> \s--------------mysql  Ver 14.14 Distrib 5.5.34, for Linux (x86_64) using  EditLine wrapperConnection id:          141700Current database:       labCurrent user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.5.34-log Source distributionProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    latin1Conn.  characterset:    latin1UNIX socket:            /tmp/mysql.sockUptime:                 47 days 4 hours 54 min 1 secThreads: 4  Questions: 189434783  Slow queries: 1  Opens: 283512  Flush tables: 23  Open tables: 46  Queries per second avg: 46.447--------------　　可以看到四种默认字符集是latin1，修改四种默认字符集，client和connection两个要是一样的。
　　set character_set_server=utf8;
　　set character_set_database=utf8;
　　set character_set_client=utf8;
　　set character_set_connection=utf8;
　　也可以修改my.cnf默认字符集
　　[mysqld]
　　character_set_server=utf8
　　[mysql]
　　default-character-set=utf8
　　重启mysql！
　　2.查看创建数据库时使用的字符集
　　show create database mydb;
　　五、创建表字段索引
　　1.主键索引
　　1.1创建一个主键索引，一般创建表时已经设置
　　alter table user add primary key(id);
　　1.2删除一个主键索引
　　alter table user drop primary key;
　　2.普通索引
　　2.1创建一个普通索引
　　alter table user add index index_name(name);
　　2.2删除一个普通索引
　　alter table user drop index index_name;
　　2.3查看主键索引和普通索引
　　show index from user;
　　show keys from user;
　　注：修改字段名不影响索引。
　　3.查看表中创建的索引
　　mysql> desc user2;
　　+-------+------------------+------+-----+---------+----------------+
　　| Field | Type             | Null | Key | Default | Extra          |
　　+-------+------------------+------+-----+---------+----------------+

　　|>　　| name  | varchar(30)      | YES  | MUL | NULL    |                |
　　| pass  | varchar(30)      | YES  |     | NULL    |                |
　　+-------+------------------+------+-----+---------+----------------+
　　3 rows in set (0.00 sec)
　　上面key中的PRI代表是主键索引，MUL代表是普通索引。
　　4.查看索引信息
　　mysql> show index from user2;
　　+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
　　+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

　　| user2 |          0 | PRIMARY    |            1 |>　　| user2 |          1 | index_name |            1 | name        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
　　+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
　　2 rows in set (0.00 sec)
　　可以看到user表，做了PRIMARY主键索引和index_name名字的普通索引。
　　5.测试是否使用索引

　　mysql> desc select>　　+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+

　　|>　　+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+
　　|  1 | SIMPLE      | user2 | ref  | index_name    | index_name | 33      | const |    1 | Using where; Using index |
　　+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+
　　1 row in set (0.00 sec)
　　用desc检测语句，可以看到查询一条张三的记录，使用了普通索引index_name这个名字，检索了一行(rows)就找到了这条记录。