mysql基本语法总结

ctrl+f：数据类型 注释

可以放在文件中，然后cmd中输入：source F:\代码。。。。\平时练习代码\数据库\selectDatas.sql

用管理员身份打开cmd
cd C:\Program Files\MySQL\MySQL Server 5.7
(其他博客：安装路径,再进去bin目录)
mysqld --install

net start mysql57

mysql -uroot -p

net start mysql57

mysql -uroot -p
use mysql;--????

修改密码：http://how2j.cn/k/mysql/mysql-changepassword/1074.html#step4132

运行sql文件：

mysql> source E:\test.sql    //mysql控制台下执行source E:\test.sql

显示数据库：

show databases;

创建、删除数据库：

create database javabook;
use javabook;

drop database <数据库名>;

创建表：

create table Course(
	courseId char(5),
	subjectId char(4) not null,
	courseNumber integer,
	title varchar(50) not null,
	numOfCredits integer,
	primary key(courseId)
);
create table Enrollment(
	ssn char(9),
	courseId char(5),
	dateRegistered date,
	grade char(1),
	primary key(ssn,courseId),
	foreign key(ssn) references Student(ssn),
	foreign key(courseId) references Course(courseId)
);

删除表：

drop table Course;

插入、更新、删除：

insert into Course (courseId,subjectId,courseNumber,title,numOfCredits)
values('1114','CSCJ','3721','Datadase Systemt',4);

update Course
set numOfCredits=4
where title='Datadase Systems';

delete from Course
where title='Datadase Systemt';

查询：
select…from…where。
not and or
like（%，_），between…and…，is null
as：别名
distinct：不重复
order by：asc升序，desc降序，默认升序

select numOfCredits,subjectId
from Course
where courseNumber='3720' and numOfCredits=4;

select numOfCredits as "NC",courseNumber
from Course
where numOfCredits between 1 and 10;

select title,50*numOfCredits as "minutes per week"
from Course
where subjectId="CSCI";


select distinct subjectId as "Subject ID"
from course;

select distinct subjectId,title
from Course;

select lastName,firstName,deptId
from Student
where deptId='CS'
order by lastName desc,firstName asc;

联结表：

select distinct lastName,firstName,courseId
from Student,Enrollment
where Student.ssn=Enrollment.ssn and lastName='Smith' and firstName='Jacob';--Student.ssn=Enrollment.ssn--

注释：属性comment

create table test1 ( 
    field_name int comment '字段的注释' 
)comment='表的注释'; 

为MYSQL加注释–mysql注释符

数据类型：
5.0版本以上，varchar(20)，指的是20 字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是： char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．

参考：
MySQL 添加注释(comment)
为MYSQL加注释–mysql注释符