[关闭]
@pspgbhu 2018-05-17T03:40:34.000000Z 字数 4518 阅读 979

mysql 笔记

mysql

MySQL 入门全套（不错的总结）
MySQL 数据库怎样把一个表的数据插入到另一个表
MySQL 之权限管理
MySQL 外键(Foreign Key)的使用
在 MySQL 数据库建立多对多的数据表关系

常用操作

设置 root 初始密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

数据库

show databases; 显示数据库
use databasename; 选择数据库
create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库，不提醒
mysqladmin drop databasename 删除数据库前，有提示。
select version(),current_date;
select database(); 显示当前数据库

数据表

show tables; 显示表
describe tablename; 表的详细描述
select 中加上distinct去除重复字段
显示当前mysql版本和当前日期
DROP TABLE table_name; 删除表

登陆 mysql

  1. mysql -u用户名 -p用户密码 -h机器IP

数据表的操作

1 查询数据

  1. SELECT column_name,column_name
  2. FROM table_name
  3. [WHERE Clause]
  4. [OFFSET M ][LIMIT N];

2 插入数据

  1. INSERT INTO table_name ( field1, field2,...fieldN )
  2.                        VALUES
  3.                        ( value1, value2,...valueN );

2.1 增

  1. insert into 表 (列名,列名...) values (值,值,...)
  3. insert into 表 (列名,列名...) values (值,值,...),(值,值,值...)
  5. insert into 表 (列名,列名...) select (列名,列名...) from 表
  7. INSERT INTO table SET row1 = value1, row2 = value2

例：

  1. insert into tab1(name,email) values('zhangyanlin','zhangyanlin8851@163.com')

如果两个表的字段一致，并且希望插入全部数据，可以用：

INSERT INTO 目标表 SELECT * FROM 来源表;

  1. insert into tab1 select * from tab2

如果只希望插入指定字段，可以用：

INSERT INTO 目标表 (字段1, 字段2, ...)
SELECT 字段1, 字段2, ... FROM 来源表;

  1. insert into tab1(id) select id from tab2;

如果您需要只导入目标表中不存在的记录，可以使用这种方法：

  1. INSERT INTO 目标表  
  2.  (字段1, 字段2, ...)  
  3.  SELECT 字段1, 字段2, ...  
  4.  FROM 来源表  
  5.  WHERE not exists (select * from 目标表  
  6.  where 目标表.比较字段 = 来源表.比较字段);

1.插入多条记录：

  1. insert into insertTest2
  2. (id,name)
  3. select id,name
  4. from insertTest
  5. where not exists (select * from insertTest2
  6. where insertTest2.id=insertTest.id);

2.插入一条记录：

  1. insert into insertTest    
  2. (id, name)    
  3. SELECT 100, 'liudehua'    
  4. FROM dual    
  5. WHERE not exists (select * from insertTest    
  6. where insertTest.id = 100);

使用 dual 作表名，select 语句后面直接跟上要插入的字段的值。

2.2 删

  1. # 删除表里全部数据
  2. delete from 表
  4. # 删除ID =1 和name='zhangyanlin' 那一行数据
  5. delete from 表 where id＝1 and name＝'zhangyanlin'

2.3 改

  1. UPDATE 表 SET name ＝ 'zhangyanlin' where id>1

set 多个字段用：

  1. UPDATE 表 SET name ＝ 'zhangyanlin', age = 12 where id > 1

2.4 查

  2. select * from 表
  3. select * from 表 where id > 1
  4. select nid,name,gender as gg from 表 where id > 1

a、条件判断where

  1. select * from 表 where id > 1 and name != 'aylin' and num = 12;
  2. select * from 表 where id between 5 and 16;
  3. select * from 表 where id in (11,22,33)
  4. select * from 表 where id not in (11,22,33)
  5. select * from 表 where id in (select nid from 表)

b、通配符like

  1. select * from 表 where name like 'zhang%'  # zhang开头的所有（多个字符串）
  2. select * from 表 where name like 'zhang_'  # zhang开头的所有（一个字符）

c、限制limit

  1. select * from 表 limit 5;            - 前5行
  2. select * from 表 limit 4,5;          - 从第4行开始的5行
  3. select * from 表 limit 5 offset 4    - 从第4行开始的5行

d、排序asc，desc

  1.     select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
  2.     select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
  3.     select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

e、分组group by

  2.     select num from 表 group by num
  3.     select num,nid from 表 group by num,nid
  4.     select num,nid from 表  where nid > 10 group by num,nid order nid desc
  5.     select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
  6.     select num from 表 group by num having max(id) > 10
特别的：group by 必须在where之后，order by之前

用户操作

1 新建用户

解决 Field 'ssl_cipher' doesn't have a default value，直接使用GRANT命令创建用户

1.1 创建用户

  1. CREATE USER 'username'@'host' IDENTIFIED BY 'password';

1.2 用户授权

  1. mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, REFERENCES ON <databaseName>.*  TO 'user'@'localhost';

查看当前用户权限: show grants
刷新权限: flush privileges

1.3 直接用 GRANT 命令创建并授权

  1. mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE ON <databaseName>.*  TO 'user'@'localhost' IDENTIFIED BY 'password';

2 删除用户

  1. DROP USER 'username'@'host';

3 查看用户列表

用户相关的信息都存在了 mysql.user 这个表下，一般情况下我们查看 user, host, password 字段就可以了。

  1. SELECT user,host,password FROM mysql.user;

4 修改用户信息

4.1 修改密码

4.1.1 直接在 mysql.user 表中修改
  1. mysql> use mysql
  2. mysql> update user set password = password(”new_password”) where user = “user_name”;
  3. mysql> flush privileges;

其实这种方法就是更新一条数据库记录，与普通update语句不同的是，密码加密存储，需用password()函数来生成，另一个不同点是需要刷新权限表。

4.1.2 在数据库中运行set password
  1. mysql> set password for user_name = password(”new_password”);
  2. mysql> flush privileges;

同第一种方法，也要刷新权限表

4.1.3 GRANT 语句
  1. grant all privileges on db.table to user_name@localhost identified by “your_pwd”;

用户名密码的生效不必用flush privileges刷新。

db.table: db表示授权哪个库，table是相应库里的表。可以用.表示所有库所有表。注意，如果想表示某个库的所有表，必须用db_name.，后面的”.”不可省略，否则权限将无法赋予。
user_name@localhost: user_name表示用户名，localhost表示该用户只能在本地访问该库，可以用%表示从任何地方访问该库，也可以用111.11.22.33来表示地址
your_pwd: 给用户设置的密码

键与索引

1 外键

MYSQL外键(Foreign Key)的使用

数据库的设计

1. 一对多

2. 多对多数据表关系

http://13145200724.blog.51cto.com/6263780/1370753

其他

1. 执行sql 文件

  1. 在mysql 中 执行
    source <sql file path>
  2. 在终端中 执行
    mysql –u<user> –p -D<database> < <filepath>

如果sql 文件中有use <database> 的 命令，则不需要加 -D

错误处理

1. ERROR 1045

http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw

2. BLOB, TEXT, GEOMETRY or JSON column 'content' can't have a default value

http://www.cnblogs.com/sharkuo/archive/2011/03/01/1968175.html
报错的原因在于

1、 MYSQL5.x是不允许BLOB/TEXT类型的字段拥有默认值的。

2、 由于MYSQL是在‘strict mode’严格模式下工作的，如果改为非严格模式，上面的语句就可以执行成功

3、 MYSQL5.x在windows下是默认以‘strict mode’工作的，当执行上面的语句时，会给你一个错误或者警告信息

建议默认值最好设置为 null，而不是空字符串

实践

1. WHERE 操作多组数据 --- WHERE IN 语句

  1. SELECT * FROM articles
  2.     WHERE id IN (1,2,3)
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注