MYSQL

MySQL

mysql

Posted by JBNRZ on 2022-11-11
Estimated Reading Time 9 Minutes
Words 1.6k In Total
Viewed Times

环境 Ubuntu 20.04 vmware

常用

1
2
3
select schema_name from information_schema.schemata;
select table_name from inforamtion_schema.tables where table_schema=database();
select column_name from information_schema.columns where table_schema=database();

安装

安装 mysql server

1
2
sudo apt-get update
sudo apt-get install mysql-server

mysql
mysql

第一次登录是无需输入密码

1
mysql -u root -p

mysql

设置 root 密码

1
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pwd';

mysql

退出

1
quit

mysql

语法

DDL

创建新用户

1
CREATE USER 'jbn'@'localhost' IDENTIFIED BY 'your_pwd';

mysql

赋予权限

1
GRANT ALL PRIVILEGES ON *.* TO 'jbn'@'localhost' WITH GRANT OPTION;

查看所有 database

1
show databases;

mysql

创建新的 database

1
crearte database test_db;

mysql

查询创建 database 的语句

1
show create database test_db;

mysql

删除 database

1
drop database test_db;

mysql

使用 database

1
use test_db;

mysql

显示当前 database 所有 table

1
show tables;

mysql

显示某 table 的表结构

1
desc table_name;

mysql

查询某表的创建语句

1
show create table table_name;

mysql

创建 table

1
2
3
4
5
6
7
create table test_table(
    id int comment "num",
    name varchar(50) comment "name",
    age int comment "age"
) comment "user_table";

// id, name, age 为字段值

mysql

添加字段

1
alter table test_table add nickname varchar(20);

mysql

修改字段

1
2
3
alter table test_table change nickname username varchar(30) comment "username";

// alter table <table name> change <old name> <new name> <type> comment "<new name>";

mysql

删除字段

1
2
3
alter table test_table drop username;

// alter table <table name> drop <name>;

mysql

修改 table 名称

1
2
3
alter table test_table rename to rename_test_table;

// alter table <old table name> rename to <new table name>;

mysql

删除 table

1
2
3
4
drop table test_table;

// drop table <table name>;
// drop table if exist <table name>

mysql

删除 table 并重新创建（删除所有数据，但是不改变表结构）

1
2
3
truncate table test_table;

// truncate table <table name>;

mysql

DML

插入值

1
2
3
insert into test_table(id, name, age) values(1, "test", 18);

// insert into <table name>(<column 1, column 2, ...>) values(<value1, value2, ...>);

mysql

批量插入值

1
2
3
insert into test_table(id, name, age) values(2, "test2", 19), (3, "test3", 19);

// insert into <table name>(<column 1, column 2, ...>) values(<value1, value2, ...>), (<value1, value2, ...>), ...;

给全部字段插入值

1
2
3
insert into test_table values(2, "test2", 19);

// insert into <table name> values(<value1, value2, ...>);

批量字段插值

1
2
3
insert into test_table values(2, "test2", 19), (3, "test3", 19);

// insert into <table name> values(<value1, value2, ...>), (<value1, value2, ...>), ...;

mysql

修改数据

1
2
3
4
update test_table set name="test";
update test_table set name="test4" where id=1;

// update <table name> set <column_name>=<value> [where <condition>]

mysql
mysql

删除数据

1
2
3
delete from test_table where name="test";

// delete from <table name> [where <condition>]

mysql
mysql

DQL

查询

1
select * from test_table;

mysql

1
select id from test_table;

mysql

1
select id, name from test_table;

mysql

1
select id name from where id=3;

mysql

1
select id as 'num' from test_table;

mysql

1
select distinct name from test_table;

mysql

条件查询

1
2
3
select * from test_table where id = 1;
select * from test_table where id <= 3;
select * from test_table where age is null;

mysql
mysql

1
2
select * from test_table where id != 3;
select * from test_table where id <> 3;

mysql

1
2
select * from test_table where id < 3 and id > 1;
select * from test_table where id < 3 && id > 1;

mysql

1
2
select * from test_table where id = 1 or id = 2;
select * from test_table where id in(1, 2);

mysql

1
2
3
4
select * from test_table where name like "test1";
select * from test_table where name like "test_";
select * from test_table where name like "____";
select * from test_table where name like "%3";

mysql

聚合查询

1
2
3
4
5
select count(*) from test_table;
select avg(age) from test_table;
select max(age) from test_table;
select min(age) from test_table;
select sum(age) from test_table;

mysql

分组查询

1
2
3
4
5
select count(name) from test_table group by name;
select name, count(name) from test_table group by name;
select name, count(name) from test_table group by name having count(name) >= 2;
select name, count(name) num from test_table group by name having count(name) >= 2;
select name, count(name) num from test_table where id > 1 group by name having count(name) >= 2;

mysql

排序查询

1
2
3
select name from test_table order by age;
select name from test_table order by age asc;
select name from test_table order by age desc;

mysql

1
2
3
4
select * from test_table order by name, age;
select * from test_table order by name, age desc;

// 当 name 相同时按照 age 排序

mysql

分页查询

1
2
3
4
select * from test_table limit 1, 3;
select * from test_table limit 2, 3;
select * from test_table limit 4, 3;
select * from test_table limit 1, 6;

mysql

DCL

查询用户

1
2
use mysql;
select * from user;

mysql

创建用户

1
2
3
4
5
6
create user "test"@"localhost" identified by "test";

// create user "<username>"@"<host>" identified by "<password>";

// create user "test"@"%" identified by "test" 
// % 为各个主机都能操作

mysql

修改用户密码

1
2
3
alter user "test"@"localhost" identified with mysql_native_password by "pass";

// alter user "<username>"@"<host>" identified with mysql_native_password by "<new pwd>"

mysql

删除用户

1
2
3
drop user "test"@"localhost";

// drop user "<username>"@"<host>"

mysql

权限控制

以普通用户 test 登录，查数据库 VS root 用户查数据库

1
2
3
show grants for 'test'@'localhost';

// show grants for "<username>"@"<host>";

mysql

授权
1
2
3
grant all on test_db.* to 'test'@'localhost';

// grant <privilage> on <database>.<table> to "<username>"@"<localhost>";

mysql
mysql

取消授权
1
2
3
revoke all on test_db.* from 'test'@'localhost';

// revoke <privilage> on <database>.<table> from '<username>'@'<host>';

mysql

Function

字符串函数

1
select concat("hello", "world");

mysql

1
2
select lower('TEST');
select upper('test');

mysql

1
2
3
4
select lpad('test', 10, '*');
select rpad('test', 10, '*');

// select [rpad | lpad]('<string>', <length>, 'string');

mysql
mysql

1
2
3
select trim('  hello world  ');

// 去除两头的空格

mysql

1
2
3
select substring('hello world', 3, 5);

// select substring('<string>', start, length);

mysql

数值函数

1
2
3
select ceil(4.5);

// 向上取整

mysql

1
2
3
select floor(4.5);

// 向下取整

mysql

1
2
3
select mod(5, 4);

// 取模

mysql

1
2
3
select rand();

// random(0, 1)

mysql

1
2
3
select round(4.98767890987, 5);

// 保留小数位

mysql

时间函数

1
2
3
select curdate();
select curtime();
select now();

mysql

1
2
3
select year("2022-11-13");
select month("2022-11-13");
select day("2022-11-13");

mysql

1
2
3
select date_add(now(), interval 70 day);
select date_add(now(), interval 70 year);
select date_add(now(), interval 70 month);

mysql

1
select datediff('2020-11-13', now());

mysql

流程控制

1
2
3
4
5
6
7
8
select if(1=1, "value1", "value2");
select if(1=2, "value1", "value2");
select ifnull("test", "default");
select ifnull("", "default");
select ifnull(null, "default");

// IF(value, true, false)
// IFNULL(value1, value2)

mysql
mysql

1
2
3
4
5
6
7
8
select id, (
    case name 
    when "test3" then "true" 
    when "test2" then "false" 
    else "null" end) 
 from test_table;

// select <column>, (case <column> when <"value"> then <"value"> else <"value"> end) from <table name>

mysql

Constraint

用于插入和修改字段时使用

创建表

1
2
3
4
5
6
7
create table user(
    id int primary key auto_increment comment "main key",       -- 主键，自增值
    name varchar(10) not null unique comment "username",        -- 不能为空，不重复
    age int check (age > 0 && age <=120) comment "user age",    -- 条件
    status char(1) default "1" comment "user status",           -- 默认值
    gender char(1) comment "user gender"                        --
) comment "user_table";

mysql

插入值

1
2
insert into user(name, age, status, gender) values('test1', 19, '1', 'm');
insert into users values("test2", 19, '1', 'w'),("test3", 21, '0', 'm');

mysql

如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。 也欢迎您共享此博客，以便更多人可以参与。 如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。 谢谢 ！

特色标签
MYSQL
链友
ESC