复习mysql数据库

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

这里顺便讲个题外话,mysql中有utf8utf8mb4两种编码,在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

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,可以远程任意电脑访问

其它

 

 

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

show databases;

show information_schema;

mysql> create table if not exists `fishtb` (
    -> `mid` int auto_increment,
    -> `name` char(40) not null,
    -> `age` int not null,
    -> 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)


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 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)


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

 

发布了357 篇原创文章 · 获赞 48 · 访问量 135万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览