常用数据库 SQL 命令详解（上）

CREATE DATABASE IF NOT EXISTS `库名` default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE DATABASE IF NOT EXISTS test_db default charset utf8mb4 COLLATE utf8mb4_unicode_ci;

RENAME database olddbname TO newdbname

# 将db1库备份到db1.sql文件
mysqldump -u root -p db1 > /usr/db1.sql;

# 导入备份文件到新库db2
mysql -u root -p db2 < /root/db1.sql;

# 删除旧库（如果真的需要）
DROP DATABASE db1;

#!/bin/bash
# 假设将db1数据库名改为db2
# MyISAM直接更改数据库目录下的文件即可

mysql -uroot -p123456 -e 'create database if not exists db2'
list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='db1'")

for table in $list_table
do
    mysql -uroot -p123456 -e "rename table db1.$table to db2.$table"
done

DROP DATABASE db1;

USE db2;

CREATE TABLE ts_user (
  id bigint(20) unsigned NOT NULL COMMENT '编码',
  name varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户姓名',
  mobile varchar(11) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '手机号',
  create_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_userid varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '更新人',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_create_time (create_time) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

ALTER  TABLE ts_user RENAME TO ts_new_user;

DROP TABLE ts_new_user;

show full columns from ts_user;

ALTER TABLE ts_user add column gender tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别，1，男；2，女' AFTER mobile;

ALTER TABLE ts_user modify column mobile varchar(30) NOT NULL DEFAULT '' COMMENT '用户手机号';

ALTER TABLE ts_user drop column gender;

 SHOW INDEXES FROM ts_user;

alter table ts_user add index idx_id (id);

alter table ts_user add unique idx_id (id);

alter table ts_user add primary key idx_id (id) ;

alter table ts_user add index idx_id_name (id,name) ;

alter table ts_user add fulltext idx_id (id) ;

# 删除普通索引
alter table ts_user drop index idx_id;

# 删除主键索引
alter table ts_user drop primary key;

select * from ts_user;

select id, name from ts_user;

select id, name from ts_user where name = '张三'

select id, name from ts_user where name = '张三' or name = '李四'

select id, name from ts_user where name in ('张三', '李四')

select id, name from ts_user where name like '张%'

select id, name from ts_user where name is not null

select id, name, age from ts_user where  age >= 18 and age <= 30

select id, name, age from ts_user where age between 18 and 30

select 
name,
(
case
when scope >= 90 then  '优'
when 80 <= scope < 90 then  '良'
when 80 > scope >= 70  then  '中'
else '差'
end
) as judge
from ts_user

select tu.id, tu.name,tr.role_name
from ts_user tu
left join ts_role tr on tu.id = tr.user_id

select tu.id, tu.name,tr.role_name
from ts_user tu
right join ts_role tr on tu.id = tr.user_id

select tu.id, tu.name,tr.role_name
from ts_user tu
inner join ts_role tr on tu.id = tr.user_id

select tu.id, tu.name,tr.role_name
from ts_user tu
full join ts_role tr on tu.id = tr.user_id

select count(id) from ts_user

select max(age) from ts_user

select min(age) from ts_user

select id, sum(score) from ts_user group by id

select id, avg(score) from ts_user group by id

select id, avg(score) from ts_user group by id having avg(score)  > 100

INSERT INTO ts_user(id, name) VALUES ('1', '张三');

INSERT INTO ts_user(id, name)
VALUES
('1', '张三'),
('2', '李四'),
('3', '王五');

update ts_user set name = '李四1', age = '18' where id = '1'

# 删除表全部内容
delete from ts_user

# 根据判断条件进行删除
delete from ts_user where id = '1'

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

create view <视图名称>[(column_list)]
       as select语句
       with check option;

create view v_user(用户名,年龄)
as
select user_name,age from t_user
with check option;

show create view v_user;

select * from v_user;

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

drop view IF EXISTS v_user;