MySQL上手

mysql -u root -p

-u:username

-p:password

 

1.库级操作：

show databases; 显示所有数据库

create database a;

drop database a; //从删库到跑路

 

2.表操作:

use a;

create table users (username varchar(32), balance double);

show tables;

desc users; //描述表的详细结构 

alter table users add column password varchar(64);

 

drop table users;

show tables;

 

//数据结构上的 drop alter

//数据上的 delete update

 

3.列级操作

create table users(

   username varchar(32),

   password varchar(63),

  balance double

);

 

alter table users change column username name varchar(12);

alter table users change column name name varchar(64);

alter table users modify name varchar(128);

 

altert table users drop column balance;

alter table users modify name varchar(32),change password passwd varchar(64);

 

4.数据操作

desc users;

select name,passwd from users;

//empty set;

select * from users;

//empty set;

insert into users (name,passwd) values ('whh','123')

select * from users;

//...

insert into users (name,passwd) values

     ('lsd','123'),

     ('lbb','321'),

     ('zks','233');

 

//delete删除数据层面的 drop是删除结构层面的

delete from users; //全部删除

delete from users where name = 'whh';

select * from users where name = 'lsd';

 update users set passwd = '233' where name = 'zks';

 

//连接

CREATE TABLE `grade` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `grade` int(11) NOT NULL,
  PRIMARY KEY (`no`)
)

INSERT INTO grade VALUES ('1', '90');
INSERT INTO grade VALUES ('2', '80');
INSERT INTO grade VALUES ('3', '70');
-- ----------------------------

CREATE TABLE `student` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`no`)
)

INSERT INTO student VALUES ('1', 'a');
INSERT INTO student VALUES ('2', 'b');
INSERT INTO student VALUES ('3', 'c');
INSERT INTO student VALUES ('4', 'd');

-- ----------------------------

mysql> select * from grade;
+----+-------+
| no | grade |
+----+-------+
|  1 |    90 |
|  2 |    80 |
|  3 |    70 |
+----+-------+
3 rows in set

mysql> select * from student;
+----+------+
| no | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
4 rows in set

内连接 inner join（查找条件中对应的数据，no4没有数据不列出来） 

在表中存在至少一个匹配时，INNER JOIN 关键字返回行

mysql> select * from student inner join grade on student.no=grade.no;
 
+----+------+----+-------+
| no | name | no | grade |
+----+------+----+-------+
|  1 | a    |  1 |    90 |
|  2 | b    |  2 |    80 |
|  3 | c    |  3 |    70 |
+----+------+----+-------+
3 rows in set

 

左连接（左表中所有数据，右表中对应数据） 

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

mysql> select * from student left join grade on student.no=grade.no; 
+----+------+------+-------+
| no | name | no   | grade |
+----+------+------+-------+
|  1 | a    |    1 |    90 |
|  2 | b    |    2 |    80 |
|  3 | c    |    3 |    70 |
|  4 | d    | NULL | NULL  |
+----+------+------+-------+
4 rows in set

 

右连接（右表中所有数据，左表中对应数据） 

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

mysql> select * from student right
 join grade on student.no=grade.no; 
+----+------+----+-------+
| no | name | no | grade |
+----+------+----+-------+
|  1 | a    |  1 |    90 |
|  2 | b    |  2 |    80 |
|  3 | c    |  3 |    70 |
+----+------+----+-------+
3 rows in set

 

group_concat:

 

 

 创建一个数据库结构相同的表:

create table newTable as select * from oldTable where 2=1;