复习mysql数据库

参考教程地址：https://www.runoob.com/mysql/mysql-functions.html

这里顺便讲个题外话，mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记**utf8**，永远使用**utf8mb4**。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。

教程地址：https://www.jb51.net/article/146051.htm
mysql文件下载地址：https://dev.mysql.com/downloads/mysql/
1.管理员权限启动cmd进入bin目录
2.执行mysqld --initialize --console

当忘记密码时：

1、删除data目录

2、重新运行 mysqld --initialize --console

启动mysql服务

启动服务:net start mysqld
关闭服务:net stop mysqld 

 手动启动mysql或者开机自启动mysql服务

3.密码：wEvttSUge6,-

    mysql  -u root  -p 

    密码：wEvttSUge6,-

     

--初始化，会打印数据库密码，记住该密码，后面会用到
mysqld --initialize --console
  
--这里MySql8是服务的名字，你可以不写或者自己命名一个服务
mysqld --install localmysql
  
--这里是启动服务名字，就是刚刚命名的名字
net start localmysql
  
--登陆数据库，这时会让你输入密码
mysql -u root -p
  
--密码过于难记，修改密码为：123456
set password for root@localhost='123456';
  
--显示所以数据库
show databases;

4.安装服务：mysqld --initialize --install
5.启动服务：net start mysql
6.停止服务：net stop mysql
7.卸载服务：mysql --remove
8.进入命令模式：mysql -u root -p
9.更改密码：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pch2015';
10.查看当前所有数据库：show databases;
11.使用存在的数据库：use information_schema;
12.查看当前数据库中的表：show tables;

13.创立用户密码：

create user  'pch'@'localhost'   identified by 'lxy2015';   --建立用户pch只能本机访问数据库

create  user  'pch'@'%'    identified  by 'lxy2015';   --建立用户pch可以远程任意电脑访问数据库

mysql> create user 'pch'@'host' identified by 'pch2015';
Query OK, 0 rows affected (0.03 sec)

14.给用户分配权限

mysql> grant all on fishdb.* to 'pch'@'%';
Query OK, 0 rows affected (0.02 sec)

15.删除用户

drop user 'pch'@'host';  --删除用户pch，只能本地访问数据库的

drop user 'pch'@'%'    --删除用户pch，可以远程任意电脑访问

其它

16.建立数据库

--第一种--
create database if not exists `fishdb`;
--第二种--
create database fishdb;

显示数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| fishdb             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

删除数据库：

mysql> drop database fishdb;

建立表，表中字符串最大长度：

（1.）

例如TEXT最大长度2的16次方 pow(2,16)=65536

LONGTEXT最大长度2的32次方 pow(2,32)=4294967296

（2）char与varchar区别

       CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，比如，存储字符串“abc”，对于CHAR(10)，表示你存储的字符将占10个字节（包括7个空字符），而同样的VARCHAR2（10）则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度储存。

       CHAR的效率比VARCHAR2的效率稍高。

建立表，二进制类型，参考：https://www.cnblogs.com/no-celery/p/13501957.html

 建立表：

use fishdb;
drop table if exists user_info;
drop table if exists item_info;

create table if not exists user_info(
mid int auto_increment comment '编号id',
mname varchar(40) default '空' comment '名字',
age int default 10 comment '玩家年纪',
money double default 2.1 comment '存款',
avatar mediumblob not null,
primary key (mid)
)engine=innodb  default charset=utf8mb4  collate=utf8mb4_unicode_ci  row_format=compact auto_increment=190 comment="玩家信息表";

测试：

mysql  -u   root  -p;
输入密码：xxxx

show databases;

show information_schema;

mysql> create table if not exists `fishtb` (
    -> `mid` int auto_increment comment '鱼id',
    -> `name` char(40) not null comment '鱼名称',
    -> `age` int not null       comment '鱼年纪',      
    -> primary key ( `mid` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;


mysql> show columns from `fishtb`;

mysql> select * from fishtb;

mysql> insert into fishtb (name,age) values ("胸大1",18);
Query OK, 1 row affected (0.01 sec)

mysql> select * from fishtb;
+-----+---------+-----+
| mid | name    | age |
+-----+---------+-----+
|   1 | 胸大1   |  18 |
+-----+---------+-----+
1 row in set (0.00 sec)


mysql> insert into fishtb (name,age) values ("腿长",19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from fishtb;
+-----+---------+-----+
| mid | name    | age |
+-----+---------+-----+
|   1 | 胸大1   |  18 |
|   2 | 腿长    |  19 |
+-----+---------+-----+
2 rows in set (0.00 sec)

条件语句where
mysql> select age,name from fishtb where age>18;
+-----+--------+
| age | name   |
+-----+--------+
|  19 | 腿长   |
+-----+--------+
1 row in set (0.00 sec)


将age=83的记录中name更改为"腿长1"
mysql> update fishtb set name="腿长1",age=83 where mid=2;


删除一条记录
mysql> delete from fishtb where name="腿长1";

mysql> select * from fishtb;
+-----+----------------+-----+
| mid | name           | age |
+-----+----------------+-----+
|   1 | 脑袋           |  28 |
|   2 | 胸大1          |  18 |
|   3 | 腰肢           |  19 |
|   4 | 屁股           |  17 |
|   6 | 腰肢           |  20 |
|   7 | 腰肢           |  20 |
|   8 | 腰肢           |  20 |
|   9 | 腰肢1          |  20 |
|  10 | 腰肢2828hhhh   |  82 |
+-----+----------------+-----+
9 rows in set (0.00 sec)

like语句模糊查询
mysql> update fishtb set name='妖艳',age=73  where name like '%腰肢%' and age>19;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

联合多语句union
mysql> select name,age,mid from fishtb where age=28  union select name,age,mid from fishtb where age=17;
+--------+-----+-----+
| name   | age | mid |
+--------+-----+-----+
| 脑袋   |  28 |   1 |
| 屁股   |  17 |   4 |
+--------+-----+-----+
2 rows in set (0.01 sec)

mysql> select name,age,mid from fishtb where age=28  union select name,age,mid from fishtb where age=17 order by age;
+--------+-----+-----+
| name   | age | mid |
+--------+-----+-----+
| 屁股   |  17 |   4 |
| 脑袋   |  28 |   1 |
+--------+-----+-----+
2 rows in set (0.00 sec)

排序order.  升序(asc)，降序(desc)
mysql> select * from fishtb where mid>1 order by mid asc;
+-----+---------+-----+
| mid | name    | age |
+-----+---------+-----+
|   2 | 胸大1   |  18 |
|   3 | 腰肢    |  19 |
|   4 | 屁股    |  17 |
|   6 | 妖艳    |  73 |
|   7 | 妖艳    |  73 |
|   8 | 妖艳    |  73 |
|   9 | 妖艳    |  73 |
|  10 | 妖艳    |  73 |
+-----+---------+-----+
8 rows in set (0.00 sec)

mysql> select * from fishtb where mid>1 order by mid desc;
+-----+---------+-----+
| mid | name    | age |
+-----+---------+-----+
|  10 | 妖艳    |  73 |
|   9 | 妖艳    |  73 |
|   8 | 妖艳    |  73 |
|   7 | 妖艳    |  73 |
|   6 | 妖艳    |  73 |
|   4 | 屁股    |  17 |
|   3 | 腰肢    |  19 |
|   2 | 胸大1   |  18 |
+-----+---------+-----+
8 rows in set (0.00 sec)




--相同name的有多少条。例如name="妖艳的"有多少条记录--
mysql> select name,count(*) from fishtb group by name;
+---------+----------+
| name    | count(*) |
+---------+----------+
| 脑袋    |        1 |
| 胸大1   |        1 |
| 腰肢    |        1 |
| 屁股    |        1 |
| 物资    |        1 |
| 妖艳    |        5 |
+---------+----------+
6 rows in set (0.00 sec)

--倒序取最后十条
mysql> select * from item_info order by mid desc limit 10;


--相同age的有多少条。例如age=73有多少条记录--
mysql> select age,count(*) from fishtb group by age order by age desc;
+-----+----------+
| age | count(*) |
+-----+----------+
|  83 |        1 |
|  73 |        5 |
|  28 |        1 |
|  19 |        1 |
|  18 |        1 |
|  17 |        1 |
+-----+----------+
6 rows in set (0.00 sec)

count sum，  with rollup
mysql> select name,sum(age) from fishtb group by name with rollup;
+---------+----------+
| name    | sum(age) |
+---------+----------+
| 妖艳    |      365 |
| 屁股    |       17 |
| 物资    |       83 |
| 胸大1   |       18 |
| 脑袋    |       28 |
| 腰肢    |       19 |
| NULL    |      530 |
+---------+----------+
7 rows in set (0.00 sec)


显示表的键primary key、index、foreign
mysql> show index from item_info;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| item_info |          0 | PRIMARY    |            1 | item_id     | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| item_info |          1 | fk_user_id |            1 | user_id     | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)



USE mydatabase;
-- INSERT INTO users (NAME,age) VALUES ('大哥',38),('二哥',37),('三哥',28),('四哥',20),('五哥',18);
-- DELETE FROM users WHERE MID>5;
-- SHOW COLUMNS FROM incomes;

-- SELECT * FROM  incomes;
-- DROP TABLE if EXISTS incomes;
-- CREATE TABLE if NOT EXISTS incomes (
-- `index` BIGINT AUTO_INCREMENT,
-- `mid` INT NOT NULL,
-- `gold` INT DEFAULT 0,
-- PRIMARY KEY (`INDEX`)
-- )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

-- SHOW COLUMNS FROM incomes;
-- INSERT INTO incomes (MID,gold) VALUES (2,1000),(3,3000),(2,199),(5,2999),(4,737);
-- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
-- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
-- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
SELECT a.index,a.mid,a.gold,b.name FROM incomes a INNER JOIN users b where a.mid=b.mid and a.gold>1000;

SELECT * FROM incomes WHERE gold IS NULL;



-- CREATE table if not exists testalter(
-- `INDEX` BIGINT AUTO_INCREMENT,
-- `MID` INT DEFAULT 0,
-- `COUNT` INT DEFAULT 0,
-- `MDES` CHAR(50) DEFAULT NULL,
-- PRIMARY KEY (`INDEX`)
-- )ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;
-- -- 
-- INSERT INTO testalter (MID,COUNT,MDES) VALUES (3,83,"yyy"),(7,98,"8wlw");
-- ALTER TABLE testalter ADD  father CHAR(20);
-- ALTER TABLE testalter DROP COUNT;
-- ALTER TABLE testalter DROP father;
-- ALTER TABLE testalter ADD mom CHAR(80) DEFAULT 'hh';
-- SELECT * FROM testalter;
-- ALTER TABLE testalter ADD son CHAR(80) DEFAULT 'bb' AFTER MID;
-- ALTER TABLE testalter MODIFY son CHAR(10);
ALTER TABLE testalter CHANGE son CHILD CHAR(128)  DEFAULT "heh";
-- SHOW COLUMNS FROM testalter;



-- 复制表
--  SHOW CREATE TABLE users ;
 CREATE TABLE if NOT EXISTS  `usercp` (
  `mid` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(100) DEFAULT NULL,
  `age` int(11) DEFAULT '0',
  `home` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;

-- INSERT INTO TABLE usercp (`mid`,`name`,`age`,`home`) SELECT (mid,name,age,home) FROM users;  -- 无效
-- insert into usercp SELECT * FROM users;
select * FROM usercp;

-- 计算长度
SELECT CHAR_LENGTH('你很好8') AS le;
-- 连接多个字符串。结果：你 是笨蛋
SELECT CONCAT('你',' ','是','笨蛋') AS re;

-- 连接多个字符串-- 结果：你=我=他
SELECT CONCAT_WS ('=','你','我','他') AS RE;

-- 返回字符串 c 在指定字符串中的位置。 结果：3
SELECT FIND_IN_SET("c", "a,b,c,d,e");

-- 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。
SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56

-- 从字符串 s 中获取 s1 的开始位置-- 
SELECT LOCATE('e','myteststring');  -- 4

-- 字符串 RUNOOB 转换为小写：
SELECT LCASE('RUNOOB') -- runoob

-- 返回字符串 runoob 中的前两个字符：
SELECT LEFT('runoob',2) -- ru

	
-- 字符串 RUNOOB 转换为小写
SELECT LOWER('RUNOOB') -- runoob

-- 将字符串 xx 填充到 abc 字符串的开始处：填充后长度到7
SELECT LPAD('abc',7,'xx') -- xxxxabc

-- 去掉字符串 RUNOOB开始处的空格：
SELECT LTRIM("    RUNOOB") AS LeftTrimmedString;-- RUNOOB

-- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：
SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO

	
-- 返回字符串 abc 中 b 的位置：
SELECT POSITION('b' in 'abc') -- 2

-- 将字符串 abc 中的字符 a 替换为字符 x：
SELECT REPLACE('abc','a','x') --xbc

	
-- 将字符串 abc 的顺序反过来：
SELECT REVERSE('abc') -- cba

-- 返回字符串 runoob 的后两个字符：
SELECT RIGHT('runoob',2) -- ob


-- 比较字符串：比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
SELECT STRCMP("runoob", "runoob");  -- 0

-- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：
SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO

	
-- 去掉字符串 RUNOOB 的首尾空格：
SELECT TRIM('    RUNOOB    ') AS TrimmedString;

	
-- 将字符串 runoob 转换为大写：
SELECT UCASE("runoob"); -- RUNOOB

-- 返回 -1 的绝对值：
SELECT ABS(-1) -- 返回1


-- 返回数据表 Products 中字段 Price 的最大值：
SELECT MAX(Price) AS LargestPrice FROM Products;

-- 返回当前日期
SELECT CURDATE();  -- 2018-09-19

-- 返回当前日期
SELECT CURRENT_DATE(); -- 2018-09-19

-- 返回当前时间
SELECT CURRENT_TIME();   -- 19:59:02

-- 返回当前日期和时间
SELECT CURRENT_TIMESTAMP();    -- 2018-09-19 20:57:43

--  计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02');  --  -32

-- 返回当前日期和时间
SELECT LOCALTIMESTAMP();   -- 2018-09-19 20:57:43

	
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0

• 命令行执行脚本：mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】

C:\program\apache-tomcat-9.0.33\bin>mysql -uroot -ppch2015 -Dbcuser < c:\work\db\fish_bcuser.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

C:\program\apache-tomcat-9.0.33\bin>mysql -uroot -ppch2015 -Dbcgame < c:\work\db\fish_bcgame.sql
mysql: [Warning] Using a password on the command line interface can be insecure.


C:\program\apache-tomcat-9.0.33\bin>mysql -uroot -ppch2015 -Dfishing_game_0301 < c:\work\db\fishing_game.sql
mysql: [Warning] Using a password on the command line interface can be insecure.