apt-get
install mysql-
server
pa aux |
grep mysql
sudo service mysql start
sudo service mysql status
./
start_navicat
CRUD
create database db1;
create database db2 charset=
utf8;
create database db3 character set utf8;
show create database db2;
alter database db1 charset=
utf8;
show databases;
use db3;
select database();
drop database db2;
use db3;
show tables;
create table mv(
id int, name
char(
20), yr year);
show create table mv;
alter table mv add dct_name
char(
20);
alter table mv drop dct_name;
alter table mv modify name varchar(
20);
alter table mv change
id id_num smallint;
select * from
mv;
insert into mv (
id, name) values(
1,
"Fast and Furious");
insert into mv values(
2,
"The Zootopia",
2018),(
3,
"The Gifts",
2019);
update mv set yr=
‘2018‘;
truncate mv;
delete from mv;
delete from mv where yr=
2017;
create table t_pk(id int primary key, name
char(
10));
create table t_atp(id int atuo_increment primary key, name
char(
10));
create table t_un(id int unique, name
char(
10));
create table t_nn(id int, name varchar(
10) not
null);
create table t_dft(id int, name varchar(
10) default
"WW");
create table t_f(id int primary key, name
char(
10));
create table t_s(id int primary ket auto_increment, name
char(
10), fid
int, foreign key(fid) references t_f(
id));
drop table t_s;
drop table t_f;
create table t_chk(id int, age
int check(age >
0 and age <
150), gender
char(
10) check(
‘boy‘ or
‘girl‘));
alter table t_pk add constraint PK_id primary key(id);
alter table t_pk drop primary key;
alter table t_fk add constraint FK_id foreign key(id) references t_pk;
desc table t_fk;
alter table t_fk drop foreign key FK_id
mysqldump -uroot -p OriginS >
bakp.sql
mysqldump -uroot -p OriginS t_s1, t_s2 >
s1s2.sql
create database OSdb;
exit;
mysql -uroot -p OSdb <
bakp.sql
select *
from t_s1;
select c_id, c_name, c_addr from t_s1;
select c_id as 学号, c_name as 姓名,c_addr 地址 from t_s1;
select distinct c_addr from t_s1;
select * from t_s1 where c_age <
18;
select * from t_s1 where c_age =
23 and c_gender =
‘girl‘;
select * from t_s1 where c_name like
‘‘D
";
select * from t_ s1 where c_id
in range(
5);×
select * from t_s1 where c_id
in (
1,
3,
8,
9)
select * from t_s1 where c_age is not
null;
select *
from goods order by c_price asc;
select *
from goods order by c_time desc;
select *
from goods order by c_price asc, c_time desc;
select * from t_s1 limit
3;
select * from t_s1 limit
2,
10;
select c_gender from t_s1 group by c_gender;
select c_gender, c_addr from t_s1 group by c_gender, c_addr;
select c_gender ,group_concat(name) from t_s1 group by c_gender;
select gender, max(age), min(age), avg(age),
sum(age), count(age) from t_s1 group by gender;
select gender, group_concat(name) from t_s1 group by gender having gender =
‘girl‘;
select gender, group_concat(name) from t_s1 where age <
25 group by gender having gender =
"girl";
select gender from t_s1 group by gender with rollup
select t_s.name, t_c.name from t_s, t_c where t_s.c_id = t_c.
id;
select s.name 学生姓名, c.name 班级名称 from t_s s, t_c c where s.c_id = c.
id;
select s.name 学生姓名, c.name 班级名称 from t_s as s inner
join t_c c on s.c_id = c.
id;
select s.name 学生姓名, c.name 班级名称 from t_s s left
join t_c c on s.c_id = c.
id;
select s.name 学生姓名, c.name 班级名称 from t_c c right
join t_s s on s.c_id = c.
id;
select * from t_s1 where age > (
select avg(age) from t_s1);
select * from t_c where
id in (
select c_id from t_s1);
select * from t_s1 where (age, class_id) = (
select max(age), min(class_id) from t_s1);
create table areas(id int primary key, atitle varchar(
20), pid(
int));
source ./
areas.sql;
select count(*) from areas where pid is
null;
select c.* from areas as c inner
join areas p on c.pid = p.
id where p.atitle =
‘湖南省‘
MySQL的一些指令操作[简版]
标签：where mys crud cat start odi set nbsp price
