Linux MySQL 学习笔记-基本语句与函数
1 基本sql语句(增,删,改,查)
1.1 库操作:
> show databases; #列出所有的库
> create database db1; #创建库
> show create database db2; #查看所创建库的属性
> use db1; #切换库
> drop database db2; #删除库
1.2 表操作
> show tables; #列出当前库中所有的表
> create table t1 (id int(10),name char(10)); #创建表,至少要有一个字段,多个字段用","隔开
> desc t1; #查看表结构
> show create table t10\G #查看表的创建属性
\G逐行读取
mysql> show create table t1\g
+-------+-------------------------------------------------------------------- --------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------- --------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(10) DEFAULT NULL,
`name` char(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------- --------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) DEFAULT NULL,
`name` char(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
alter
> alter table t1 rename to t10; #修改表名
> alter table t1 add sex char(10); #添加字段在末位
> alter table t1 add QQ int after name; #添加字段在指定字段的后面
> alter table t1 add uid int first; #添加字段在首位
> alter table t1 drop uid; #删除字段
> alter table t1 change id uid int; #修改字段的名称及属性
> alter table t1 modify uid int(10); #修改字段的属性
> truncate stu_info; # 清空表
> drop table t2; #删除表
alter 添加字段
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t1 add class_id int(10) after id;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| class_id | int(10) | YES | | NULL | |
| name | char(15) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
alter修改
mysql> alter table t1 modify name char(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| class_id | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
mysql> alter table t1 change id stu_id int(15);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t1;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| stu_id | int(15) | YES | | NULL | |
| class_id | int(10) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
1.3 记录操作
> insert into t1 set uid=1,name='tom',sex='male'; #添加一条记录
> insert into t1 (uid,name,sex) values (2,'jim','male');
> insert into t1 (uid,name,sex) values (3,'lilei','male'),('4','lily','female'); #添加多条记录
> insert into t1 values (5,'lucy',123,'female'); #注意:列数和值数一定要一样
> update t1 set name='hanmeimei' where uid=6; #修改记录,注意定位
> update t1 set uid=6 where name='lucy' and QQ is null; #多条件定位 and ↔ && or ↔ || not ↔ !
> delete from t1 where uid=6; #删除记录,注意定位
insert
mysql> insert into t1 set stu_id=1,name='tom';
mysql> select * from t1;
+--------+----------+------+
| stu_id | class_id | name |
+--------+----------+------+
| 1 | NULL | tom |
+--------+----------+------+
1 row in set (0.00 sec)
mysql> insert into t1 (stu_id,name) values(2,'tom');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+--------+----------+------+
| stu_id | class_id | name |
+--------+----------+------+
| 1 | NULL | tom |
| 1 | NULL | tom |
| 2 | NULL | tom |
+--------+----------+------+
delete
mysql> delete from t1 where stu_id=1;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from t1;
+--------+----------+------+
| stu_id | class_id | name |
+--------+----------+------+
| 2 | NULL | tom |
+--------+----------+------+
创建表score
mysql> create table score(
-> Stu_id int(10),
-> Name char(20),
-> Sex char(4),
-> Math int(10),
-> English int(10));
mysql> desc score;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| Stu_id | int(10) | YES | | NULL | |
| Name | char(20) | YES | | NULL | |
| Sex | char(4) | YES | | NULL | |
| Math | int(10) | YES | | NULL | |
| English | int(10) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
mysql> insert into score Values
-> (123,'Tom','Male',56,23),
-> (124,'Rick','Male',66,43),
-> (125,'Morty','Male',86,56);
mysql> select * from score;
+--------+-------+------+------+---------+
| Stu_id | Name | Sex | Math | English |
+--------+-------+------+------+---------+
| 123 | Tom | Male | 56 | 23 |
| 124 | Rick | Male | 66 | 43 |
| 125 | Morty | Male | 86 | 56 |
+--------+-------+------+------+---------+
类似脚本的方法
mysql -u root -p -e "select * from db1.score"
Enter password:
+--------+-------+------+------+---------+
| Stu_id | Name | Sex | Math | English |
+--------+-------+------+------+---------+
| 123 | Tom | Male | 56 | 23 |
| 124 | Rick | Male | 66 | 43 |
| 125 | Morty | Male | 86 | 56 |
+--------+-------+------+------+---------+
类脚本插入数据
[root@localhost ~]# declare -A user1
[root@localhost ~]# user1[Name]=Henry
[root@localhost ~]# user1[id]=4
[root@localhost ~]# user1[sex]=Male
[root@localhost ~]# user1[Math]=$[RANDOM%51+50]
[root@localhost ~]# user1[English]=$[RANDOM%51+50]
[root@localhost ~]# user1[Stu_id]=4
[root@localhost ~]# user1[Sex]=Male
[root@localhost ~]# mysql -u root -p -e "insert into db1.score values (${user1[Stu_id]},'${user1[Name]}','${user1[Sex]}',${user1[Math]},${user1[English]})"
Enter password:
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> select * from db1.score;
+--------+-------+------+------+---------+
| Stu_id | Name | Sex | Math | English |
+--------+-------+------+------+---------+
| 123 | Tom | Male | 56 | 23 |
| 124 | Rick | Male | 66 | 43 |
| 125 | Morty | Male | 86 | 56 |
| 4 | Henry | Male | 86 | 81 |
+--------+-------+------+------+---------+
4 rows in set (0.00 sec)
取henry的英语数学成绩
mysql> select Math,English from score where Name='Henry';
+------+---------+
| Math | English |
+------+---------+
| 86 | 81 |
+------+---------+
使用通配符查找
以H开头的
mysql> select Math,English from score where Name like 'H%';
+------+---------+
| Math | English |
+------+---------+
| 86 | 81 |
+------+---------+
大于70分的
1 row in set (0.00 sec)
mysql> select Math,English from score where Math>=70;
+------+---------+
| Math | English |
+------+---------+
| 86 | 56 |
| 86 | 81 |
+------+---------+
2 rows in set (0.00 sec)
1.4 单表查询
> select * from t1; #查询表中所有字段的所有记录.如果显示很乱,用\G结尾
> select name,qq from t1; #查询部分字段的所有记录
> select name,chinese from t2 where chinese >= 80; #查询部分字段的部分记录
> select name,chinese,english from t2 where chinese >= 80 and english = 100; #多条件定位
> select 姓名,语文 from score where 语文 between 61 and 79; # 相当于 语文 >= 61 and 语文 <= 79
> select 姓名,籍贯 from info where 籍贯 in('广东','广西'); # 用in代替or
> select * from t2 order by chinese; #以指定字段值升序排列
> select * from t2 order by chinese desc; #desc: 逆序
> select * from t2 order by chinese desc limit 2; #只显示前几条记录
> select * from t2 where english=(select max(english) from t2); #子查询
> select * from t2 where name like 't%'; #like通配符 %: 所有 _: 任意单个字符
> select * from t2 where name regexp '^t'; #regexp正则表达式
> select name,chinese+math+english as total from t2; #as别名
> select count(name) from t2; #统计name字段有多少记录, null不会被统计
> select count(1) from t2; #统计有多少条记录
> select sex,count(sex) from t1 group by sex; #统计指定字段中相同的值的个数
1.4 多表查询
- 内连接: 两个表某字段的值完全一样
select t1.name,t1.sex,t2.math from t1 join t2 on t1.name=t2.name;
select t1.name,t1.sex,t2.math from t1,t2 where t1.name=t2.name;
- 外连接: 左,右连接
左: 显示出左表中所有的记录
右: 显示出右表中所有的记录
select t1.name,t1.sex,t2.math from t1 left join t2 on t1.name=t2.name; #左连接
select t2.name,t1.sex,t2.math from t1 right join t2 on t1.name=t2.name; #右连接
2 函数
database() select database(); #查看当前所在的库
user() select user(); #查看当前登陆的用户
password() #给字符串加密,一般用于修改密码
2.1 聚合函数
sum() select sum(age) from t1; #显示age字段的和
avg() select avg(age) from t1; #求age字段的平均值
max() select max(age) from t1; #最大值
min() select min(age) from t1; #最小值
count() select sex,count(sex) from t1 group by sex; #统计sex字段每种有多少条记录
select count(*) from pass; 统计表中有多少条非空的记录
2.2 时间函数
1.查看当前时间
curtime() #select curtime();
2.查看当前日期
curdate()
3.查看系统时间
now()
sysdate()
2.3 其他函数
1.连接函数 concat()
MariaDB [test]> select concat(username,uid) from pass;
MariaDB [test]> select concat(username,' ',uid) from pass;
MariaDB [test]> select concat(username,':',uid) from pass;
MariaDB [test]> select concat(username,':',uid) as 'username-uid' from pass;
2.转换成小写 lower()
MariaDB [test]> update pass set username='ROOT' where username='root';
MariaDB [test]> select lower(username) from pass;
3.转换成大写 upper()
MariaDB [test]> select upper(username) from pass;
4.求长度 length()
MariaDB [test]> select username,length(username) from pass;
3 练习题
- 在qianfeng库中创建passwd表,有以下字段:
uid 整型
gid 整型
user_name 字符串,宽度为20
home_dir 字符串,宽度为20
shell 字符串,宽度为30
mysql> use qianfeng
Database changed
mysql> create table passwd (uid int(10),gid int(10),user_name char(20),home_dir char(20),shell char(30));
Query OK, 0 rows affected (0.00 sec)
- 将home_dir字段的宽度改为30
mysql> alter table passwd modify home_dir char(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc passwd;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| uid | int(10) | YES | | NULL | |
| gid | int(10) | YES | | NULL | |
| user_name | char(20) | YES | | NULL | |
| home_dir | char(30) | YES | | NULL | |
| shell | char(30) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 将系统中所有用户信息录入到passwd表
#!bin/bash
while read line;do
uid=$(echo $line|awk -F: '{print $3}')
gid=$(echo $line|awk -F: '{print $4}')
user_name=$(echo $line|awk -F: '{print $1}')
home_dir=$(echo $line|awk -F: '{print $6}')
shell=$(echo $line|awk -F: '{print $7}')
mysql -u root -p "insert into qianfeng.passwd values ($uid,$gid,'$user_name','$home_dir','$shell')"
done < /etc/passwd
- 统计用户总数
select count(user_name) from passwd;
- 统计gid为0的用户数
select count(gid) from passwd where gid=0;
- 打印出gid为0的所有用户的用户名,gid
select user_name,gid from passwd where gid=0;
- 按照uid从大到小打印出所有用户的用户名,uid,shell,只打印前3名
select user_name,uid,shell from passwd order by uid desc limit 3;
- 打印出gid最小的用户信息
select * from passwd where gid=(select min(gid) from passwd);
- 打印可登录用户的用户名及登录shell
select user_name,shell from passwd where shell regexp 'sh$';
- 打印出用户名以s开头的用户名及uid
select user_name,uid from passwd where user_name regexp '^s';
- 打印出用户名以s开头,且只有4个字符的用户名及uid
select user_name,uid from passwd where user_name regexp '^s' and user_name like '___';
- 打印出uid和gid相同的用户名,uid,gid
select user_name,uid,gid from passwd where uid = gid;
- 打印所有uid为奇数的用户名,uid
select user_name,uid from passwd where uid%2=1;
- 打印出uid在8以内(不含8)的所有用户信息
select * from passwd where uid<8;
- 分类统计出每种登录shell的数量,并按从多到少的顺序打印出来
select shell,count(shell) from passwd group by shell order by count(shell) desc;
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
