Mysql基础操作

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)

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> 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=3G;

//　　加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)

帮助手段：

? 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> 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加一个字段

原来是：

往id前加字段：

mysql> alter table post add uid int after id;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0