MySQL学习笔记

数据库名字：test_mysql

数据库表名字：test_table

win7环境mysql服务启停：net start/stop mysql

Linux环境mysql服务启停：/etc/init.d/mysqld start/stop/status/restart

1、创建数据库

create database test_mysql;  小写字母创建
CREATE DATABASE test_mysql;  大写字母创建

2、创建表

create table test_table (id int primary key, value varchar(255));
create table test_table_copy as select * from test_table;    采用复制另一张表的方式创建表，新表与旧表中数据一致，但无法复制主键类型和自增方式
create table test_table_copy like test_table;                所有数据类型和完整性约束条件都能被复制，包括主键和自动增长，但是无法指定属性或属性组，新建的数据库为空表
insert into test_table_copy select * from test_table;        往新建的表中复制旧表中的所有数据

3、插入

INSERT INTO test_table (id, value) VALUES (6, 4282023451287445924);

4、删除表

drop table test_table;              删除表本身，表中数据、表中结构被全部删除
delete from test_table;             删除表中的数据，但是表还存在
delete from test_table where ID=1;  删除表中指定条件的数据
truncate table test_table;          删除表中的数据，但是表还存在，不能跟where子句

5、查询表结构

desc test_table;
show columns from test_table;
show full fields from test_table;       查看权限和备注等信息

查询结果字段说明：
Field:字段表示的是列名
Type:字段表示的是列的数据类型
Null:字段表示这个列是否能取空值
Key:在mysql中key 和index 是一样的意思，这个Key列可能会看到有如下的值：PRI(主键)、MUL(普通的b-tree索引)、UNI(唯一索引)
Default: 列的默认值
Extra:其它信息

6、查询表状态

show table status like "test_table";

7、修改数据库字段名字、备注

ALTER TABLE test_table CHANGE old_value new_value VARCHAR(255);
alter table test_table modify column field_name varchar(255) comment '备注';

8、增加数据库字段

ALTER TABLE test_table ADD address VARCHAR(255);

9、删除数据库字段

ALTER TABLE test_table DROP address;

10、主键和外键

（1）主键：关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键，主键只能有一个，用来保证数据完整性

（2）外键：表的外键是另一表的主键，外键可以有重复的，可以是空值，用来和其他表建立联系用的，一个表可以有多个外键

CREATE TABLE mainkey (number VARCHAR(255) PRIMARY KEY, data INT);
CREATE TABLE foreignkey (no VARCHAR(255), CONSTRAINT fk_mainkey FOREIGN KEY (no) REFERENCES mainkey(number), data INT);
CREATE TABLE foreignkey1 (no VARCHAR(255), CONSTRAINT fk1_mainkey FOREIGN KEY (no) REFERENCES mainkey(number) ON UPDATE CASCADE, data INT);
UPDATE mainkey set number="123456789" where data=123;   执行该语句更新主键mainkey中的数据，会同时更新外键foreignkey1中的数据，因为设置了ON UPDATE CASCADE

11、视图

       关系型数据库中的数据是由一张一张的二维关系表所组成，简单的单表查询只需要遍历一个表，而复杂的多表查询需要将多个表连接起来进行查询任务。对于复杂的查询事件，每次查询都需要编写MySQL代码效率低下。为了解决这个问题，数据库提供了视图（view）功能。

       和数据库相对应，每次进行查询工作，都需要编写查询代码进行查询；而视图的作用就是不必每次都重新编写查询的SQL代码，而是通过视图直接查询即可。

CREATE VIEW test_view as select * from test;    单表视图
CREATE VIEW multi_view as select test.value, test.phone, TEST_UP.test_data from test, TEST_UP where test.phone=TEST_UP.test_data;     多表视图
ALTER VIEW test_view as select value, phone from test; 修改视图，增加或者删除视图中的字段
drop view test_view;
show create view test_view;
show table status like 'test_view';

12、聚合函数

在数据库当中，函数分为两种：单行函数和多行函数，相应概念如下：
单行函数：每一行返回一个数值（如lower）
多行函数：多行返回一个数值（如count）
聚合函数：多行函数，即表中的多条记录返回至一个数值，通常用于分组的相关信息

常用聚合函数：

• count(*)：统计表中所有记录的个数
• count(列名)：统计一列中值的个数，其中重复的记录也会被当做有效的记录。
• count(distinct 列名)：统计一列中值的个数，其中重复的记录只会被记录一次。
• sum(列名)：计算一列值的总和。
• avg(列名)：计算一列值的平均值。
• max(列名)：计算一列值中的最大值。
• min(列名)：计算一列值中的最小值。

select sum(id) from test_table;
select count(distinct address) from test_table;
select count(*) com from test_table;
select count(phone) from test_table;

13、mysql查看用户

use mysql;      切换到mysql database
select user from user;   查看表user中已经存在的用户信息

14、mysql新增用户

CREATE USER test_user IDENTIFIED BY "test1234";     为MySQL创建新用户

15、mysql用户分配权限

GRANT语法：
GRANT 权限 ON 数据库.* TO 用户名@'登录主机' IDENTIFIED BY '密码'  
权限：  
    ALL,ALTER,CREATE,DROP,SELECT,UPDATE,DELETE  
    新增用户：权限为USAGE,即为："无权限",想要创建一个没有权限的用户时,可以指定USAGE  
数据库：  
    *.*                       表示所有库的所有表  
    test_mysql.*              表示test_mysql库的所有表  
    test_mysql.test_table     表示test_mysql库的test_table表   
用户名：  
    MySQL的账户名，如“test_user”
登陆主机：  
    允许登陆到MySQL Server的客户端ip  
    '%'             表示所有ip  
    'localhost'     表示本机  
    '10.69.123.23'  特定IP  
密码：  
    MySQL的账户名对应的登陆密码，如“test1234”


命令举例：
GRANT ALL ON test_mysql.* TO "test_user"@"%" IDENTIFIED BY "test1234";    分配test_user拥有test_mysql库中所有表的操作权限，且可以从任何客户端登录到mysql的服务端进行操作
FLUSH PRIVILEGES;    刷新权限
SHOW GRANTS for test_user;   查看赋予用户test_user的权限

16、mysql用户撤销权限

命令：REVOKE PRIVILEGES ON database.tablename FROM "username"@"host";
例如：REVOKE delete ON test_mysql.test_table FROM "test_user"@"%";

17、mysql设置root用户权限

set password for root@localhost = password('123');   直接设置密码

use mysql; 
update user set password=password('123') where user='root' and host='localhost';   修改user表中数据的方式进行修改
flush privileges;

18、mysql用户删除

DROP USER "test_user"@"%";

19、存储过程

use test_mysql;
set @original_data="13112345678,
13212345678,
13312345678,
13412345678"; -- 定义源数据

set @len=4;  -- 计算数据的长度，以逗号分隔为一个数据
set @data=replace(@original_data,"\n","");  -- 去掉源数据中的换行符

采用存储过程，往表中循环插入源数据
DROP PROCEDURE IF EXISTS proc_initData;   -- 存在则删除
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=@len DO
	set @sub_data=substring_index(substring_index(@data,",",i),",",-1);
        INSERT INTO test_table(id,phone) VALUES(i, @sub_data);
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();


语法说明：
1、字符串截取：substring(str, pos); substring(str, pos, len)
2、字符串截取：substring_index(str,delim,count)，截取第count个'delim'之前的所有字符
    举例：
    截取第二个'.'之前的所有字符
    mysql> select substring_index('www.example.com', '.', 2);

    截取第二个'.'（倒数）之后的所有字符
    mysql> select substring_index('www.example.com', '.', -2);
3、字符串替换：replace(object,search,replace_str)，把object中出现search的全部替换为replace_str

20、mysql中的function

查看数据库的函数功能是否开启：

show variables like '%func%';                   查看函数功能是否开启
SET GLOBAL log_bin_trust_function_creators=1;   开启函数功能
SET GLOBAL log_bin_trust_function_creators=0;   关闭函数功能

结合存储过程和自定义函数，往数据库中循环插入数据：

use test;
set @original_data="13112345678,
13212345678,
13312345678,
13412345678"; -- 定义源数据

set @len=4;  -- 计算数据的长度，以逗号分隔为一个数据
set @data=replace(@original_data,"\n","");  -- 去掉源数据中的换行符

采用自定义函数，往表中插入一条数据
DROP FUNCTION IF EXISTS my_insert_func;    -- 存在则删除
DELIMITER $
CREATE FUNCTION my_insert_func(x INT, d VARCHAR(255))
returns INT
BEGIN
    INSERT INTO test_table(id,phone) VALUES(x, d);
    return x;
END $

采用存储过程，调用自定义函数，往表中循环插入源数据
DROP PROCEDURE IF EXISTS my_insert_proc;    -- 存在则删除
DELIMITER $

CREATE PROCEDURE my_insert_proc()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=@len DO
	set @sub_data=substring_index(substring_index(@data,",",i),",",-1);
        select my_insert_func(i, @sub_data);       采用调用函数的方式，往数据中插入数据
        SET i = i+1;
    END WHILE;
END $
CALL my_insert_proc();

 

 

备注：

MySQL常见错误类型：https://www.cnblogs.com/yangliheng/p/5871202.html