mysql篇

已于 2023-05-18 23:02:56 修改
阅读量351 收藏
点赞数
分类专栏： 常用汇总 文章标签： mysql sql
于 2020-01-31 15:12:49 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/BCMY0110/article/details/104114253
版权

MySQL篇

常用操作

  • mysqld --console 启动MySQL服务器
  • mysqladmin -uroot shutdown 关闭MySQL服务器
  • mysql -u <user> -p 登录到数据库
  • select user from mysql.user; 查询用户
  • select host,user,authentication_string from user; 查询用户连接方式密码

基本上手操作相关语句


// 查询用户
select user from mysql.user;
执行结果:
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
3 rows in set (0.00 sec)

// 查询用户
 select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          |                                           |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | maoyu         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

// 新增用户
create user maoyu@'%' identified by '123456';

//  给用户增加操作权限
// GRANT ALL(代表全部权限) ON 数据库.表 to 用户@访问方式
GRANT ALL PRIVILEGES ON maoyu.* to 'maoyu'@'%';

// 刷新权限
FLUSH PRIVILEGES;

// 查询用户和密码
select user,authentication_string from mysql.user;
+---------------+-------------------------------------------+
| user          | authentication_string                     |
+---------------+-------------------------------------------+
| root          |                                           |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-------------------------------------------+
3 rows in set (0.00 sec)

// 查看当前拥有的数据库
show databases;
执行结果:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

// 创建数据库	create database 数据库名称
create database maoyu;
执行结果:
Query OK, 1 row affected (0.00 sec)

// 切换数据库	use 数据库
use maoyu;
执行结果:
Database changed

// 创建表 设置主键 create table 表名 (字段名 类型 primary key：设置主键 not nul：非空)
create table student (id INT primary key, name VARCHAR(10) not null,age TINYINT);	

// 遍历表格
show tables;
执行结果:
+-----------------+
| Tables_in_maoyu |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

// 查看表格结构 desc 表名
desc student;
执行结果:
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | NO   |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

// 创建表格设置主键自增
// primary key：主键	auto_increment：自增	not null：非空
create table demo0 (id INT primary key auto_increment,name VARCHAR(10)not null);
表格结构：
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

// 创建表格设置主键自增，设置默认值 
// default: 默认值
 create table demo1 (id INT primary key auto_increment ,
 					 name VARCHAR(10) not null,
 					 age TINYINT default 18);

表格结构：
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | 18      |                |
+-------+-------------+------+-----+---------+----------------+

// 删除表 drop table 表名
drop table demo0;

// 插入数据 insert into 表名 value (对应列字段的值)
insert into student values (1,'hello',15);
执行结果:
Query OK, 1 row affected (0.01 sec)

// 插入数据 insert into 表名 (字段) value (对应列字段的值)
insert into student (id,name,age) values (2,'word',16);

//  插入数据
insert into student (id,name) values (3,'phone');

// 查询表 select 列名 from 表名;
select * from student;
执行结果:
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | hello |   15 |
|  2 | word  |   16 |
|  3 | phone | NULL |
+----+-------+------+
3 rows in set (0.00 sec)

// 查询表
select id,name from student;
+----+-------+
| id | name  |
+----+-------+
|  1 | hello |
|  2 | word  |
|  3 | phone |
+----+-------+

// 更改数据 update 表名 set 列名 where 条件表达式
update student set age=17 where id=3;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | hello |   15 |
|  2 | word  |   16 |
|  3 | phone |   17 |
+----+-------+------+

// 删除表中一条数据 delete from 表明 where 条件表达式
 delete from student where id=3;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | hello |   15 |
|  2 | word  |   16 |
+----+-------+------+

MySQL提升

// 查询 + AS: 字段别名 
select id as '索引',name as '名称' from student;
+------+-------+
| 索引 | 名称  |
+------+-------+
|    1 | hello |
|    2 | word  |
+------+-------+

// 多表查询 student表和contact表
1. select student.id,student.name,contact.phone from student,contact;
2. select student.*,contact.phone from student,contact;
+----+-------+--------+
| id | name  | phone  |
+----+-------+--------+
|  1 | hello | 123456 |
|  2 | word  | 123456 |
|  1 | hello | 456321 |
|  2 | word  | 456321 |
+----+-------+--------+

// 多表查询 + 条件显示
select student.*,contact.phone from student,contact where student.name=contact.name;
+----+-------+------+--------+
| id | name  | age  | phone  |
+----+-------+------+--------+
|  1 | hello |   15 | 123456 |
|  2 | word  |   16 | 456321 |
+----+-------+------+--------+

// 多行插入
insert into mobile value ('sony',600,800),('samsung',900,1300);
+---------+-----------+--------------+
| name    | costPrice | sellingPrice |
+---------+-----------+--------------+
| sony    |       600 |          800 |
| samsung |       900 |         1300 |
+---------+-----------+--------------+

// 查询 + 别名 + 计算
select *,sellingPrice-costPrice as '盈利' from mobile;
+---------+-----------+--------------+------+
| name    | costPrice | sellingPrice | 盈利 |
+---------+-----------+--------------+------+
| sony    |       600 |          800 |  200 |
| samsung |       900 |         1300 |  400 |
+---------+-----------+--------------+------+

// 查询 + 别名 + 聚合函数MAX() 
select max(costPrice) as '最高成本价' from mobile;
+------------+
| 最高成本价 |
+------------+
|        900 |
+------------+

// 查询 + 别名 + 聚合函数MIN()
 select min(costPrice) as '最低成本价' from mobile;
+------------+
| 最低成本价 |
+------------+
|        600 |
+------------+

// 查询 + DISTINCT：去重(去除重复) 
select distinct * from contact;
+-------+--------+
| name  | phone  |
+-------+--------+
| hello | 123456 |
| word  | 456321 |
+-------+--------+
去重之前：
+-------+--------+
| name  | phone  |
+-------+--------+
| hello | 123456 |
| word  | 456321 |
| hello | 123456 |
+-------+--------+

// 查询 + LIMIT: 显示N条
select * from student limit 2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | hello |   15 |
|  2 | word  |   16 |
+----+-------+------+

// 查询 + 从N条显示到M条
select * from student limit 2,4;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | windows |   17 |
|  4 | mac     |   18 |
+----+---------+------+

// 查询 + 别名 + 计算 + 常量
select *,(sellingPrice-costPrice)*0.9 as '纳税之后' from mobile;
+---------+-----------+--------------+----------+
| name    | costPrice | sellingPrice | 纳税之后 |
+---------+-----------+--------------+----------+
| sony    |       600 |          800 |    180.0 |
| samsung |       900 |         1300 |    360.0 |
+---------+-----------+--------------+----------+

// 查询 + 无名列
 select 1+2+3;
+-------+
| 1+2+3 |
+-------+
|     6 |
+-------+

// 查询 + 无名列 + 别名
select 1+2+3 as '结果';
+------+
| 结果 |
+------+
|    6 |
+------+

// 查询 + BETWEEN: 范围查询
select * from student where id between 1 and 3;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | hello   |   15 |
|  2 | word    |   16 |
|  3 | windows |   17 |
+----+---------+------+

// 查询 +  OR: 逻辑或
select * from student where id=1 or id=2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | hello |   15 |
|  2 | word  |   16 |
+----+-------+------+

// 查询 + AND: 逻辑与
select * from student where id>1 and age>15;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
|  4 | mac     |   18 |
+----+---------+------+

// 查询 + NOT: 逻辑非
select * from student where not id=1;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
|  4 | mac     |   18 |
+----+---------+------+

// 查询 + 模糊匹配
select * from student where name like 'w%';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
+----+---------+------+

// 查询 + 模糊匹配 + 逻辑或
select * from student where name like 'w%' or name like 'h%';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | hello   |   15 |
|  2 | word    |   16 |
|  3 | windows |   17 |
+----+---------+------+

// 查询 + 集合 + 条件过滤
select * from student where id in (2,3);
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
+----+---------+------+

// 查询 + 集合 + 逻辑非
select * from student where id not in (1,2);
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | windows |   17 |
|  4 | mac     |   18 |
+----+---------+------+

// 查询 + 当前时间
select now();
+---------------------+
| now()               |
+---------------------+
| 2020-01-30 23:10:41 |
+---------------------+

// 查询 + 当前时间 + 格式化 + DATE_FORMAT()
select date_format(now(),'%Y-%c-%d');
+-------------------------------+
| date_format(now(),'%Y-%c-%d') |
+-------------------------------+
| 2020-1-30                     |
+-------------------------------+

// 查询 + 模糊匹配 %: 由0个或多个字符组成的任意字符串
select * from student where name like 'w%';
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
+----+---------+------+

// 查询 + 模糊匹配 _: 任意单个字符
select * from student where name like 'w_rd';
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | word |   16 |
+----+------+------+

// 查询 + 模糊匹配 + 定义转义字符
select * from student where name like 'demo%' escape '#';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  5 | demo% |   19 |
+----+-------+------+

// 查询 + 空值
select * from demo where name is null;
+------+------+
| id   | name |
+------+------+
|    0 | NULL |
+------+------+

// 查询 + 非空值
select * from demo where name is not null;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+

// 查询 + 处理空值
select *,isnull(name) as '是否为空值' from demo;
+------+-------+------------+
| id   | name  | 是否为空值 |
+------+-------+------------+
|    0 | NULL  |          1 |
|    1 | zhang |          0 |
+------+-------+------------+

// 查询 + 升序排序 ORDER BY: 排序 字段 ASC: 升序
select * from student order by age asc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | LiSi     |   10 |
|  6 | ZhangSan |   12 |
|  1 | hello    |   15 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  4 | mac      |   18 |
|  5 | demo%    |   19 |
+----+----------+------+

// 查询 + 降序排序 DESC: 降序
select * from student order by age desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | demo%    |   19 |
|  4 | mac      |   18 |
|  3 | windows  |   17 |
|  2 | word     |   16 |
|  1 | hello    |   15 |
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
+----+----------+------+

// 查询 + 多列排序: 先按age排序，如果age相同则按照id排序
select * from student order by age,id;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | LiSi     |   10 |
|  6 | ZhangSan |   12 |
|  1 | hello    |   15 |
|  8 | WangWu   |   15 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  9 | NieFeng  |   17 |
|  4 | mac      |   18 |
|  5 | demo%    |   19 |
+----+----------+------+

// 查询 + 多列排序 + 主排列与次排列
select * from student order by age asc,id desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | LiSi     |   10 |
|  6 | ZhangSan |   12 |
|  8 | WangWu   |   15 |
|  1 | hello    |   15 |
|  2 | word     |   16 |
|  9 | NieFeng  |   17 |
|  3 | windows  |   17 |
|  4 | mac      |   18 |
|  5 | demo%    |   19 |
+----+----------+------+

// 查询 + 聚合函数SUM(): 对指定列的所有非空值求和 支持类型： 数字
 select sum(age) from student;
+----------+
| sum(age) |
+----------+
|      139 |
+----------+

// 查询 + 聚合函数AVG(): 对指定列中的所有非空值求平均值 支持类型：数字
select avg(age) from student;
+----------+
| avg(age) |
+----------+
|  15.4444 |
+----------+

// 查询 + 聚合函数COUNT(): 统计结果集中全部记录行的数量
select count(*) from student;
+----------+
| count(*) |
+----------+
|        9 |
+----------+

// 查询 + 聚合函数AVG() + 条件限制
select avg(age) from student where age>15;
+----------+
| avg(age) |
+----------+
|  17.4000 |
+----------+

// 查询 + 聚合函数AVG() + 条件限制 + 去除小数尾0
select cast(avg(age) as char)+0 from student where age>15;
+--------------------------+
| cast(avg(age) as char)+0 |
+--------------------------+
|                     17.4 |
+--------------------------+

// 查询 + 聚合函数AVG() + 条件限制: 子查询
select * from student where (age > (select avg(age) from student ) );
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
|  4 | mac     |   18 |
|  5 | demo%   |   19 |
|  9 | NieFeng |   17 |
+----+---------+------+

// 查询 + 聚合函数COUNT() + 条件限制
select count(*) from student where age>15;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

// 查询 + 聚合函数SUM()：动态求值
select sum(sellingPrice - costPrice) as '盈利' from mobile;
+------+
| 盈利 |
+------+
|  600 |
+------+

// 查询 + 子查询 + 多表
select *,(select avg(student.age) from student) from contact;
+-------+--------+----------------------------------------+
| name  | phone  | (select avg(student.age) from student) |
+-------+--------+----------------------------------------+
| word  | 456321 |                                15.4444 |
| hello | 123456 |                                15.4444 |
+-------+--------+----------------------------------------+

// 查询 + 子查询 + 动态求值 + 聚合函数AVG()
select * from student where age>(select avg(age) from student);
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  2 | word    |   16 |
|  3 | windows |   17 |
|  4 | mac     |   18 |
|  5 | demo%   |   19 |
|  9 | NieFeng |   17 |
+----+---------+------+

// 查询 + 多表连接
select student.*,contact.* from contact,student;
+----+----------+------+-------+--------+
| id | name     | age  | name  | phone  |
+----+----------+------+-------+--------+
|  1 | hello    |   15 | word  | 456321 |
|  1 | hello    |   15 | hello | 123456 |
|  2 | word     |   16 | word  | 456321 |
|  2 | word     |   16 | hello | 123456 |
|  3 | windows  |   17 | word  | 456321 |
|  3 | windows  |   17 | hello | 123456 |
|  4 | mac      |   18 | word  | 456321 |
|  4 | mac      |   18 | hello | 123456 |
|  5 | demo%    |   19 | word  | 456321 |
|  5 | demo%    |   19 | hello | 123456 |
|  6 | ZhangSan |   12 | word  | 456321 |
|  6 | ZhangSan |   12 | hello | 123456 |
|  7 | LiSi     |   10 | word  | 456321 |
|  7 | LiSi     |   10 | hello | 123456 |
|  8 | WangWu   |   15 | word  | 456321 |
|  8 | WangWu   |   15 | hello | 123456 |
|  9 | NieFeng  |   17 | word  | 456321 |
|  9 | NieFeng  |   17 | hello | 123456 |
+----+----------+------+-------+--------+

// 查询 + 多表连接 + 条件限制
select student.*,contact.phone from student,contact where contact.name=student.name;
+----+-------+------+--------+
| id | name  | age  | phone  |
+----+-------+------+--------+
|  1 | hello |   15 | 123456 |
|  2 | word  |   16 | 456321 |
+----+-------+------+--------+

// 查询 + 内连接
内连接: 组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
select * from student inner join contact on student.name=contact.name;
+----+-------+------+-------+--------+
| id | name  | age  | name  | phone  |
+----+-------+------+-------+--------+
|  1 | hello |   15 | hello | 123456 |
|  2 | word  |   16 | word  | 456321 |
+----+-------+------+-------+--------+

// 查询 + 左外连接
左外连接: 左(外)连接，左表(a_table)的记录将会全部表示出来，
			而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。	

select * from student left join contact on student.name=contact.name;
+----+----------+------+-------+--------+
| id | name     | age  | name  | phone  |
+----+----------+------+-------+--------+
|  2 | word     |   16 | word  | 456321 |
|  1 | hello    |   15 | hello | 123456 |
|  3 | windows  |   17 | NULL  | NULL   |
|  4 | mac      |   18 | NULL  | NULL   |
|  5 | demo%    |   19 | NULL  | NULL   |
|  6 | ZhangSan |   12 | NULL  | NULL   |
|  7 | LiSi     |   10 | NULL  | NULL   |
|  8 | WangWu   |   15 | NULL  | NULL   |
|  9 | NieFeng  |   17 | NULL  | NULL   |
+----+----------+------+-------+--------+			

// 查询 + 右外连接
右外连接: 与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，
				而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
			
select * from student right join contact on student.name=contact.name;
+------+-------+------+-------+--------+
| id   | name  | age  | name  | phone  |
+------+-------+------+-------+--------+
|    1 | hello |   15 | hello | 123456 |
|    2 | word  |   16 | word  | 456321 |
+------+-------+------+-------+--------+			

// 查询 + 左外连接
 select student.*,contact.phone from 
 student left join contact on student.name=contact.name;
+----+----------+------+--------+
| id | name     | age  | phone  |
+----+----------+------+--------+
|  2 | word     |   16 | 456321 |
|  1 | hello    |   15 | 123456 |
|  3 | windows  |   17 | NULL   |
|  4 | mac      |   18 | NULL   |
|  5 | demo%    |   19 | NULL   |
|  6 | ZhangSan |   12 | NULL   |
|  7 | LiSi     |   10 | NULL   |
|  8 | WangWu   |   15 | NULL   |
|  9 | NieFeng  |   17 | NULL   |
+----+----------+------+--------+

// 查询 + UNION: 组合查询 
select * from student where id>5 union select * from student where name like 'w%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
|  8 | WangWu   |   15 |
|  9 | NieFeng  |   17 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
+----+----------+------+

// 查询 + 组合查询 + 返回重复的行
select * from student where id>5 union all select * from student where name like 'w%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
|  8 | WangWu   |   15 |
|  9 | NieFeng  |   17 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  8 | WangWu   |   15 |
+----+----------+------+
 
// 查询 + 组合查询 + 排序
select * from student where id>5 union 
select * from student where name like 'w%' order by id asc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
|  8 | WangWu   |   15 |
|  9 | NieFeng  |   17 |
+----+----------+------+

// 插入 + 查询 + 类型转换
insert into contact (name,phone) select name,cast(id+10000 as char) from student;
+----------+--------+
| name     | phone  |
+----------+--------+
| hello    | 10001  |
| word     | 456321 |
| hello    | 123456 |
| word     | 10002  |
| windows  | 10003  |
| mac      | 10004  |
| demo%    | 10005  |
| ZhangSan | 10006  |
| LiSi     | 10007  |
| WangWu   | 10008  |
| NieFeng  | 10009  |
+----------+--------+

// 创建表 + 拷贝
create table newTable as select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | hello    |   15 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  4 | mac      |   18 |
|  5 | demo%    |   19 |
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
|  8 | WangWu   |   15 |
|  9 | NieFeng  |   17 |
+----+----------+------+

// 更新 + 一行多列
update newTable set name='BuJingYun',age=22 where id=4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | hello     |   15 |
|  2 | word      |   16 |
|  3 | windows   |   17 |
|  4 | BuJingYun |   22 |
|  5 | demo%     |   19 |
|  6 | ZhangSan  |   12 |
|  7 | LiSi      |   10 |
|  8 | WangWu    |   15 |
|  9 | NieFeng   |   17 |
+----+-----------+------+

// 删除 + 多行删除
delete from newTable where id in(9,8,7);
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | hello     |   15 |
|  2 | word      |   16 |
|  3 | windows   |   17 |
|  4 | BuJingYun |   22 |
|  5 | demo%     |   19 |
|  6 | ZhangSan  |   12 |
+----+-----------+------+

// 创建视图
create view studentView as select * from student;
+-----------------+
| Tables_in_maoyu |
+-----------------+
| contact         |
| demo            |
| mobile          |
| newtable        |
| student         |
| studentview     |
+-----------------+

// 删除视图
drop view studentView;
+-----------------+
| Tables_in_maoyu |
+-----------------+
| contact         |
| demo            |
| mobile          |
| newtable        |
| student         |
+-----------------+

// 创建视图 + 多表 + 条件限制
 create view studentView as 
 select student.*,contact.phone from student,contact 
 where student.name=contact.name;
 +----+----------+------+--------+
| id | name     | age  | phone  |
+----+----------+------+--------+
|  1 | hello    |   15 | 123456 |
|  2 | word     |   16 | 10002  |
|  3 | windows  |   17 | 10003  |
|  4 | mac      |   18 | 10004  |
|  5 | demo%    |   19 | 10005  |
|  6 | ZhangSan |   12 | 10006  |
|  7 | LiSi     |   10 | 10007  |
|  8 | WangWu   |   15 | 10008  |
|  9 | NieFeng  |   17 | 10009  |
+----+----------+------+--------+

// 数据表修改 + 添加列  ALTER TABLE 表名 ADD 列名 类型
alter table demo add score int;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
| score | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

// 数据表修改 + 修改列的数据类型 alter table 表名 modify column 列名 数据类型;
alter table demo modify column name char(10);
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| score | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

// 数据表修改 + 修改列名 + 修改数据类型 alter table 表名change column 旧列名 新列名 数据类型;
alter table demo change column score data int;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| data  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

// 数据表修改 + 添加主键
alter table demo add primary key(id);
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| data  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

// 数据表修改 + 删除列 
 alter table demo drop column data;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+ 

// 数据表修改 + 添加自增: 自增的列必须先设为主键
alter table demo change id id int AUTO_INCREMENT;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

// 数据表修改 + 字符修改： 支持中文
alter table 表名convert to character set utf8;
alter table student convert to character set utf8;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | hello    |   15 |
|  2 | word     |   16 |
|  3 | windows  |   17 |
|  4 | mac      |   18 |
|  5 | demo%    |   19 |
|  6 | ZhangSan |   12 |
|  7 | LiSi     |   10 |
|  8 | WangWu   |   15 |
|  9 | NieFeng  |   17 |
| 14 | 大猫鱼   |   25 |
+----+----------+------+

// 创建表 + 设置字符集： 支持中文
create table demo1(id int AUTO_INCREMENT primary key,
					name char(20))character set = utf8;

有缘在更…

清晨与猫鱼
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql基础篇
wphoenix的博客
08-03 123
mysql 基础篇 1、基本查询 # mysql 连接 mysql -uroot -hlocalhost -P3306 -p # 查看数据库 show databases; # 查看当前库中所有表 show tables; # 查看指定库中所有表 show tables from 库名; # 显示指定表中所有列 show columns from 表名; # 打开/使用指定库 use 库名; # 查询基础 select 查询列表 别名 from 表名; # 查询去重 select distin
CentOS7 MySQL8.0 启动失败 Data Dictionary initialization failed
liurui50的博客
04-12 3万+
在CentOS7下面MySQL启动失败。 1.查看错误原因 1.1看错误日志在哪里 cat /etc/my.cnf 确定错误日志文件在/var/log/mysqld.log,查看错误 1.2查看错误 cat /var/log/mysqld.log 2020-04-12T14:43:42.933326Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-04...
win10安装mysql 遇到的问题
坑里水库的博客
01-23 4365
首先从官网下载了mysql的zip包，然后解压到本地 其中my.ini是我自己添加的，因为解压安装包后并没有这个文件 里面的内容我是这么写的 [mysqld] #设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=D:\mysql\mysql-8.0.14-winx64 datadir=D:\mysql\mysql-8.0.14-winx64\dat...
MYSQL安装报错 -- 出现Failed to find valid data directory.
热门推荐
github_38832708的博客
10-13 5万+
运行环境：windows10 数据库版本：mysql.8.0.12 安装方式：rpm包直接安装 问题描述： mysql初始化的时候找不到对应的数据库存储目录 报错代码： 2018-10-13T03:29:24.179826Z 0 [System] [MY-010116] [Server] D:\Program Files\MySQL\bin\mysqld.exe (mysqld 8.0.12) s...
mysql服务正在启动 服务无法启动 没有任何错误信息 （本地计算机上的mysql服务启动停止，某些服务在未由其他服务或程序使用将自动停止）
qq_45718508的博客
10-15 788
首先这个错误是这样来的，就是你的mysql目录下的data文件里面的东西都删除了但是你的data文件没有删除这就造成你在使用 mysqld --initialize-insecure --user=mysql 命令的时候他没办法帮你生成data文件 解决方案 首先你碰到的问题是这样的才可以 出现了这样的问题之后首先我们查看日志文件 2020-10-15T09:51:12.680160Z 0 [System] [MY-010116] [Server] D:\Environment\mysql -8.0
mysql 安装及常见错误排除记录
https://ligo100.cn 小何博客欢迎访问。
12-26 1965
问题 1：[ERROR] [MY-011011] [Server] Failed to find validdata directory.解决：初始化 date 的路径已经存在了，找到 C:\mysql8.0.11 目录下，把 date 文件删除掉问题 2：‘mysql’ 不是内部或外部命令，也不是可运行的程序或批处理文件。解决：没有设置系统变量，可用指令设置 path=%path%;D:\My...
面试题MySQL篇.pdf
11-18
面试题MySQL篇主要涵盖了许多关于MySQL的关键知识点，包括数据表引擎、锁机制、字符类型以及索引优化等。 1. 数据表引擎： - InnoDB是MySQL的默认事务型引擎，支持行级锁、外键和崩溃后的安全恢复，适用于需要事务...
MySQL篇.md
06-13
MySQL篇
网络安全等级保护测评-MYSQL篇.docx
05-13
网络安全等级保护测评-MYSQL篇 在网络安全中，MYSQL是一个关键的组件，需要进行严格的安全保护和测评。本文将对MYSQL的安全保护进行详细的介绍和测评，涵盖身份鉴别、入侵防范、安全审计等方面。 一、身份鉴别 ...
程序员热门面试题及详细解析MySQL篇（9）
04-13
MongoDB、MyBatis、MySQL、Netty、Nginx、Oracle、Python、RabbitMQ、 React、Redis、Spring、SpringBoot、SpringCloud、SpringMVC、Vue、Zookeeper、 并发编程、大数据、前端等。 程序员找工作面试题大集锦，收集...
【mysql】MySQL服务无法启动 NET HELPMSG 3534
想和派大星一样
08-27 1964
命令，输出如下 （一开始下载的是5.x.x的版本，这里报错让我升级版本，所以现在使用了8.1.0版本）1、删除data目录下的所有文件（data目录即是my.ini中配置的datadir）可以看到上面的[ERROR]信息：data目录初始化失败了。1、在cmd中，进入mysql的bin路径下，比如。, 然后data目录下就自动创建了很多文件。来启动mysql，然后出现了报错。mysql安装成功之后，使用。mysql版本：8.1.0。重新安装mysql服务。2、在控制台输入命令。
mysql各种神坑
qq_36912167的博客
03-18 2338
1.service mysqld start [ERROR] [MY-011011] [Server] Failed to find valid data 解決方法: a.查看/etc/my.cnf的datadir路径 vi /etc/my.cnf b.更正datadir路径 根据 Failed to find valid data directory. 修改路径 b1 找出mysql文件夹 #w...
mysql-8.0.11-winx64安装时 MySQL 服务正在启动 MySQL 服务无法启动
mukouping82的博客
07-18 2万+
按照操作网上常规步骤在mysql的根目录下编写my.ini并创建data文件夹 [mysql] # 设置mysql客户端默认字符集 default-character-set=utf8  [mysqld] #设置3306端口 port = 3306  # 设置mysql的安装目录 basedir=D:\mysql-8.0.11-winx64 # 设置mysql数据库的数据的存放目录 datadi...
mysql 8 初始化失败_MySql8.0.11，安装初始化报错
weixin_32475641的博客
01-27 2003
windows平台，zip解压安装my.ini[mysql]# 设置mysql客户端默认字符集default-character-set=utf8[mysqld]#设置3306端口port = 3306# 设置mysql的安装目录basedir=C:\Program Files\MYSQL\mysql-8.0.11-winx64# 设置mysql数据库的数据的存放目录datadir=C:\Prog...
MySQL：SELECT 语句
Zachyy的博客
07-20 373
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MYSQL
最新发布
maoqiwei的博客
07-23 82
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint 2.为Course表中Cno 课程号字段设置索引,并查看索引 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩5.删除所有索引
MySQL字段设置的varchar长度小于数据长度自动截取丢弃超出的长度而不是报错？
weixin_43831997的博客
07-23 101
当MySQL数据库设置为==严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）==时，如果尝试插入或更新超过设定长度的数据，将会导致错误，并阻止该事务进行。在默认情况下，或者在非严格模式（NO_STRICT）下，当插入长度超出 VARCHAR 字段定义的字符串时，MySQL会自动截断超出的部分，并发出一个警告，而不是错误。
"JAVA_MySQL篇.pdf：数据库事务精要，ACID特性详解，视图作用"。
MySQL是一种关系型数据库管理系统，而JAVA是一种广泛应用于开发各种软件的编程语言。在JAVA开发中，通常会涉及到与数据库交互的操作，而MySQL是其中常用的数据库之一。本文将着重讨论JAVA与MySQL在开发中的关键概念...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值