创建数据库

CREATE DATABASE d_name CHARACTER SET utf8 COLLATE utf8_bin;

• CHARACTER：指定字符集
• COLLATE：指定校对规则
  • utf8_general_ci：默认:不区分大小写
  • utf8_bin：区分大小写

创建数据库用户

创建账户:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：用户名
host：本地访问，如果需要远程访问，改成%
password：密码

例子：CREATE USER 'test'@'host' IDENTIFIED BY 'test';
//创建了一个test的用户，密码也是test,这个账号只能本地访问数据库

授权
GRANT privileges ON databasename.tablename TO 'username'@'host' 
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

例子：
GRANT all ON test TO 'test'@'host';
//给test账户访问本地test库的所有权限。

更新root权限：
update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';
更新完毕后，需要重启mysql服务哦

查询数据据

查询全部数据库：

SHOW DATABASES;

查询某个数据库创建语句：

SHOW CREATE DATABASE d_name;

备份数据库

导出数据库stu和test到D盘的data.sql中

在DOS中执行回车后输入密码即可：

mysqldump -u root -p -B stu test > d:\\data.sql

恢复数据

先登录数据库

C:\Users\Fireice-Jxb>mysql -u root -p
Enter password: ****

然后恢复数据

mysql> source d:\\data.sql

备份指定数据库中的表

mysqldump -u root -p 数据库 表1 表2... > d:\\data.sql

恢复数据和上面一样

创建表

基础语法

CREATE TABLE table_name(
    filed1 int,
    filed1 int,
    ...
)
CHARACTER SET 字符集:没有指定就按照所在数据库为准
COLLATE 校对规则:没有指定就按照所在数据库为准
ENGINE 存储引擎

列类型

数值类型

• 整数
  • tinyint[1字节]
  • bit[填1-64]
  • smallint[2字节]
  • mediumint[3字节]
  • int[4字节]
  • bigint[8字节]
• 小数
  • float[4字节]
  • double[8字节]
  • decimal[可以指定精确位数]

文本类型

• char[0-255]
• varchar[0-65535] [0-2^16-1]
• text[0-65535] [0-2^16-1]
• longtext[0-2^32-1]

二进制数据

• blob[0-2^16-1]
• longblob[0-2^32-1]

日期类型

• date[日期:年月日]
• time[时间:时分秒]
• datetime[年月日时分秒:YYYY-MM-DD HH:mm:ss]
• timestamp[时间戳]

修改表

修改表名称

RENAME TABLE user TO people;

修改字符集

ALTER TABLE people CHARACTER SET utf8;

在user表中age字段后面添加一列job并且不为空，设定默认值

ALTER TABLE user ADD job VARCHAR(20)
NOT NULL
DEFAULT '默认值'
AFTER age;

修改列

ALTER TABLE user
MODIFY job VARCHAR(30)
NOT NULL
DEFAULT '程序员'
AFTER age;

删除列

ALTER TABLE user DROP job;

修改列名称

ALTER TABLE people 
CHANGE des
descript VARCHAR(50) NOT NULL DEFAULT '';

增删改查[CRUD]

增加列

INSERT INTO people (id,name,age,descript)
VALUES (4,'李四',22,'长得真的很帅');

修改列

UPDATE people SET id=6,name='赵武' WHERE id IS NULL;

删除列

DELETE FROM people WHERE id=6;

查询去重

SELECT DISTINCT age FROM people;

查询运算＋取别名

SELECT (age+10) AS '年龄' FROM people;

WHERE运算符

1.>,<,>=,<=,=,<>,!=
2.BETWEEN ... AND ...
3.IN(set)
4.LIKE ''/NOT LIKE ''
5.IS NULL
6.and,or,not

模糊查询

SELECT name AS '名字',age AS '年龄' FROM people
WHERE name LIKE '李%';

order by子句

order by可以指定列名也可以指定列的别名，asc默认:升序，desc:降序排序

年龄从大到小

SELECT * FROM people ORDER BY age DESC;

合计函数

统计个数

SELECT COUNT(age) FROM people WHERE age>20;
#count(*)返回满足条件的记录函数
#count(列名称)统计满足条件的某列有多少个，会排除为null的情况

求总和

#年龄的总和，注意sum函数只能对数值类型使用
SELECT SUM(age) AS'年龄总和' FROM people;

求平均数

SELECT AVG(age) AS '平均年龄' FROM people;

最大最小值

SELECT MAX(age) AS '最大年龄',
MIN(age) AS '最小年龄' FROM people;

分组统计

练习表

#删除表
DROP TABLE d_user;
#创建新的表
CREATE TABLE d_user (
id INT NOT NULL PRIMARY KEY auto_increment COMMENT '序号',
name VARCHAR(20) NOT NULL DEFAULT 'name' COMMENT '姓名',
sex CHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
age int NOT NULL DEFAULT 18 COMMENT '年龄',
des VARCHAR(50) NOT NULL DEFAULT '正常人' COMMENT '描述'
);
#查询一下
SELECT * FROM d_user;
#随便插入语句
INSERT INTO d_user VALUES(DEFAULT,'张三',DEFAULT,20,'相貌俊美');
INSERT INTO d_user VALUES(DEFAULT,'李四',DEFAULT,19,'能吃是福');
INSERT INTO d_user VALUES(DEFAULT,'王五',DEFAULT,22,'相貌俊美');
INSERT INTO d_user VALUES(DEFAULT,'赵六',DEFAULT,DEFAULT,'相貌俊美');
INSERT INTO d_user VALUES(DEFAULT,'周七','女',17,'美若天仙');
INSERT INTO d_user VALUES(DEFAULT,'朱八','女',19,'天女下凡');

分组查询

SELECT MAX(age) FROM d_user GROUP BY sex;

Having往往配合group by一起使用

SELECT MAX(age) FROM d_user GROUP BY sex HAVING MAX(age) > 20;
#取别名的方式效率更高
SELECT MAX(age) AS aaa FROM d_user GROUP BY sex HAVING aaa > 20;

字符串相关函数

01. CHARSET(str)#返回字符串字符集
02. CONCAT(str1,str2,...)#连接字符串
03. INSTR(str,substr)#返回substr在str中的位置，没有就是0
04. UCASE(str)#转成大写
05. LCASE(str)#转成小写
06. LEFT(str,len)#从左取len个字符
07. LENGTH(str)#返回str的字节长度
08. REPLACE(str,from_str,to_str)#str中使用to_str替换from_str
09. STRCMP(expr1,expr2)#逐个比较字符串大小
10. SUBSTRING(str,pos,len)#从str的pos位置开始取len个字符
11. LTRIM(str)#去除左边空格
12. RTRIM(str)#去除右边空格

查询某个字符串的字符集

SELECT CHARSET(name) FROM d_user;
#utf8

连接

SELECT CONCAT(name,'的年龄是',age) AS '年龄' FROM d_user;
#连接成一列

字符位置

SELECT INSTR('abcdefg','c') FROM DUAL;
#结果：3；DUAL是系统自带的亚元表，可以作为一个测试表来使用

替换

SELECT name,REPLACE(sex,'男','boy') AS '性别' FROM d_user;

数学相关函数

01.ABS(X)#绝对值
02.BIN(N)#十进制转换为二进制
03.CEILING(X)#向上取整
04.CONV(N,from_base,to_base)#进制转换
05.FLOOR(X)#向下取整
06.FORMAT(X,D)#保留D位小数位
07.HEX(N_or_S)#转换为十六进制
08.LEAST(value1,value2,...)#求最小值
09.MOD(N,M)#求余数
10.RAND([seed])#随机数:范围0-1.0

8转化为二进制输出

SELECT CONV(8,10,2) FROM DUAL;

随机数

SELECT RAND() FROM DUAL;
#每次运行都会产生随机数

SELECT RAND(2) FROM DUAL;
#每次运行都会产生随机数，但是如果第二次运行时
#seed的值依然是2，那么随机数还是之前的
#如果每次seed不一样都会生成不一样的随机数

日期函数

01. CURRENT_DATE#当前日期
02. CURRENT_TIME#当前时间
03. CURRENT_TIMESTAMP#当前时间戳
04. DATE(expr)#返回expr中的日期部分
05. DATE_ADD(date,INTERVAL expr unit)#在date增加日期或者时间
06. DATE_SUB(date,INTERVAL expr unit)#在date减去时间
07. DATEDIFF(expr1,expr2)#求日期差(天)
08. TIMEDIFF(expr1,expr2)#求时间差(时分秒)
09. NOW()#返回当前年月日时分秒
10. YEAR(date)#获取date中的年
11. MONTH(date)#获取date中的月
12. FROM_UNIXTIME(unix_timestamp)#获取date中的年月日

创建一个练习表

#创建时间 表
CREATE TABLE time(
id INT PRIMARY KEY auto_increment COMMENT '编号',
name VARCHAR(10) NOT NULL DEFAULT '北京新闻' COMMENT '时间名称',
time DATETIME);
#插入数据
INSERT INTO time VALUES(DEFAULT,DEFAULT,CURRENT_TIMESTAMP());
INSERT INTO time VALUES(DEFAULT,'广州',NOW());
INSERT INTO time VALUES(DEFAULT,'上海',NOW());
#查询一下
SELECT * FROM time;

查询十分钟之内发布过的新闻

SELECT * FROM time WHERE DATE_ADD(time,INTERVAL 10 MINUTE) >= NOW();

假如你能活到80岁，那么你能活多少天？

SELECT DATEDIFF('2080-05-22',NOW()) FROM DUAL;

转换时间戳

#获取int类型的时间戳
SELECT UNIX_TIMESTAMP() FROM DUAL;
#通过时间戳获得Date类型
SELECT FROM_UNIXTIME('1621070046','%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

#md5加密，几乎不可逆
SELECT MD5('root');

#显示用户名和ip地址
SELECT USER() FROM DUAL;

#数据库默认密码加密
SELECT PASSWORD('root');