参考教程地址: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.