1、查看表
show tables;
2、创建表
mysql> create table user(
-> id int,
-> name varchar(30),
-> pass varchar(30)
-> );
Query OK, 0 rows affected (0.01 sec)
插入sex(不允许null，默认nan)：
mysql> alter table user add sex varchar(5) not null default "nan";
3、修改表名称
mysql> rename table user to user1;
Query OK, 0 rows affected (0.01 sec)
4、删除表
mysql> drop table user1;
Query OK, 0 rows affected (0.01 sec)
5、查看表字段
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| pass | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
6、查看表数据
select * from user;
二：表设计
mysql> create table t1(
-> id int unsigned auto_increment primary key,
-> name varchar(30),
-> sex varchar(5) not null default "nv"
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| sex | varchar(5) | NO | | nv | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
查看服务器的基本信息
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
Connection id: 2
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.16-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 3 hours 29 min 11 sec
Threads: 1 Questions: 55 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 104 Queries per second avg: 0.004
--------------
server characterset: utf8 //服务器字符集
Db characterset: uf8 //数据库字符集
Client characterset: utf8 //客户端字符集
conn. characterset: utf8 //客户端连接字符集
查看数据库字符集：
show create database test;
查看表字符集：
show create table user;
my.ini中:
[mysql]
default-character-set = utf8 //客户端和连接字符集；
[mysqld]
character-set-server = utf8 //服务器、数据库和表字符集
20161116:
检测sql语句：
desc select id,name from t1 where id=3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
desc select id,name from t1 where id=3\G;
// 加\G把表倒一下
//row 1
创建表的另一个方法（带索引）：
create table t2(
-> id int unsigned auto_increment,
-> name varchar(30),
-> primary key(id),
-> index in_named(name)
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> desc t2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
帮助手段：
? show;
查看表中的所有索引：
show index form t2;
后期维护普通索引:
1、添加普通索引
mysql> alter table t2 add index in_name(name);
2、删除普通索引
mysql> alter table t2 drop index in_named;
后期维护数据表字段：
1、添加字段
mysql> alter table t1 add age int;
2、修改字段
mysql> alter table t1 modify age int not null default 20;
3、删除字段
mysql> alter table t1 drop age;
4、修改字段名(把name改为username)
mysql> alter table t1 change name username varchar(30);
20161116/23回家
SQL语句：
1、DDL 数据定义语言
2、DML 数据操作语言
3、DQL 数据查询语言
4、DCL 数据控制语言
增-insert：
insert into user(name) values("user4");
insert into user() values();
改-update：
update t1 set username='g' where id=5;
一次该多个值
update t1 set id=5233333,username="hhhhh" where id=5;
删-delete：
delete from t1 where id=6;
查-select:
搜索like关键字，包含xx的数据
mysql> select * from user2 where name like '%4%';
+----+-------+--------+
| id | name | pass |
+----+-------+--------+
| 4 | user4 | 333 |
| 6 | user4 | 3333 |
| 7 | user4 | 444444 |
| 8 | user4 | 55555 |
| 9 | use4 | NULL |
+----+-------+--------+
5 rows in set (0.00 sec)
使用order by对查询结果排序
原来：
mysql> select * from user2;
+----+-------+--------+
| id | name | pass |
+----+-------+--------+
| 1 | user1 | 111 |
| 2 | user2 | 222 |
| 3 | user3 | 333 |
| 4 | user4 | 333 |
| 5 | user5 | 555 |
| 6 | user4 | 3333 |
| 7 | user4 | 444444 |
| 8 | user4 | 55555 |
| 9 | use4 | NULL |
+----+-------+--------+
9 rows in set (0.00 sec)
排序后：
mysql> select * from user2 order by name;
+----+-------+--------+
| id | name | pass |
+----+-------+--------+
| 9 | use4 | NULL |
| 1 | user1 | 111 |
| 2 | user2 | 222 |
| 3 | user3 | 333 |
| 4 | user4 | 333 |
| 6 | user4 | 3333 |
| 7 | user4 | 444444 |
| 8 | user4 | 55555 |
| 5 | user5 | 555 |
+----+-------+--------+
9 rows in set (0.00 sec)
降序排列(默认是升序)
mysql> select * from user2 order by id desc;
+----+-------+--------+
| id | name | pass |
+----+-------+--------+
| 9 | use4 | NULL |
| 8 | user4 | 55555 |
| 7 | user4 | 444444 |
| 6 | user4 | 3333 |
| 5 | user5 | 555 |
| 4 | user4 | 333 |
| 3 | user3 | 333 |
| 2 | user2 | 222 |
| 1 | user1 | 111 |
+----+-------+--------+
9 rows in set (0.00 sec)
20161116/23:02 结束睡觉!
20161117/09：52
分组聚合
mysql> select name,count(id) tot from mess group by name order tot desc;
//group by必须写在order by之前
by必须写在having之前，having是对分组的结果进行筛选，这里不能用where
多表查询
2表之间没有关系，建立联系举例
论坛用户和帖子统计：
mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 20 |
| 2 | user2 | 20 |
| 3 | user4 | 20 |
| 4 | user5 | 25 |
| 5 | user7 | 27 |
+----+-------+------+
5 rows in set (0.00 sec)
mysql> select * from post;
+----+------+--------------+------------------+
| id | uid | title | content |
+----+------+--------------+------------------+
| 1 | 3 | 444 | 3333344444444444 |
| 2 | 3 | 422244 | 3333344 |
| 3 | 2 | 422212111111 | 3333344 |
+----+------+--------------+------------------+
3 rows in set (0.00 sec)
mysql> select*from user,post where user.id=post.uid;
+----+-------+------+----+------+--------------+------------------+
| id | name | age | id | uid | title | content |
+----+-------+------+----+------+--------------+------------------+
| 3 | user4 | 20 | 1 | 3 | 444 | 3333344444444444 |
| 3 | user4 | 20 | 2 | 3 | 422244 | 3333344 |
| 2 | user2 | 20 | 3 | 2 | 422212111111 | 3333344 |
+----+-------+------+----+------+--------------+------------------+
3 rows in set (0.04 sec)
select user.name,post.title,post.content from user,post where user.id=post.uid;
+-------+--------------+------------------+
| name | title | content |
+-------+--------------+------------------+
| user4 | 444 | 3333344444444444 |
| user4 | 422244 | 3333344 |
| user2 | 422212111111 | 3333344 |
+-------+--------------+------------------+
3 rows in set (0.02 sec)
// name是第一张表的、title和content是第二张表的
结果2-谁发了多少个：
mysql> select user.name,count(user.id) from user,post where user.id=post.uid group by post.uid;
+-------+----------------+
| name | count(user.id) |
+-------+----------------+
| user2 | 1 |
| user4 | 2 |
+-------+----------------+
2 rows in set (0.00 sec)
任何表都要留一个表作为主键自增
mysql> create table user(
-> id int unsigned auto_increment primary key,
-> name varchar(30),
-> age int);
Query OK, 0 rows affected (0.18 sec)
mysql> create table post(
-> id int unsigned auto_increment primary key,
-> title varchar(200),
-> content text);
Query OK, 0 rows affected (0.16 sec)
给post加一个字段
原来是：
mysql> desc post;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(200) | YES | | NULL | |
| content | text | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
往id前加字段：
mysql> alter table post add uid int after id;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
