发哥要做活神仙

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理


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

 

posted on 2016-11-10 14:04  发哥要做活神仙(笔记)  阅读(98)  评论(0编辑  收藏  举报